Param on measure


(Eran Ben Moha) #1

Let’s say I’ve got a Requests and Tags tables.
They are in an explore with one request having many tags.
I want to have all requests without a certain tag - how would I approach this problem?


(Daan Visker) #2

Hi @Eran_Ben_Moha ,

If a request can have zero to many tags, I would say that the Explore is based on the Requests table and has a join on Tags with a one_to_many relationship?

Then, when you have a count measure to the Tags table, you can filter on the number of rows in Tags that are connected to a request. With that, you can Explore on Requests ‘WHERE tags.count = 0’.
This way you will only get rows with requests that have no connection with any row in the Tags table.

I hope this helps!

Kind regards,

Daan

(Below some quick example LookML that connects to what I said above)

explore: requests {

  join: tags {
    type: left_outer
    sql_on: requests.id = tags.request_id
    relationship: one_to_many
  }
}

and in the view for tags:

measure: count {
  type: count # (assuming that tags has a dimension with primary_key = yes)
}