Filter or "where" clause inside a dimension's LookML query?


#1

Is there a way to include a filter in a dimension’s LookML so that it is filtered before even being queried? I’m not sure if I’m phrasing this correctly, but here is the scenario:

We have multiple fields in our database table called “fuel_type”, which each have different attributes such as “system_type” tied to them. I want to be able to create separate dimensions so that Dimension A pulls in the fuel_type when the system_type = “x”; and Dimension B pulls in the fuel_type when system_type = “y”.

This would allow me to add multiple fuel_type dimensions to the same report without adding a report filter which would limit other data. Is this possible?

I tried adding a subquery on my dimension so that it looked like:
sql: (SELECT fuel_type from ${TABLE} where system_type = 'heatingsystem' );;

But this returned a PostgresSQL error that “more than one row returned by a subquery used as an expression”, which is actually what I’m trying to accomplish, since in this scenario, there are more than one heatingsystems which would have more than one fuel types.

Is there a different way to write this kind of expression?


#2

Hey @mike.manzi,

You’re totally on the right track with the subquery method, we just need to define that subquery in a different place; the derived_table parameter!

The subquery can be defined within the derived table and then dimensions and measures defined based on the derived table. If you would like to have just one view and allow the user to decide which system_type they want in the subquery, templated filters or parameters can be used!