The Problem
You want to create a dimension that cannot be selected in the UI, but you want to be able to filter on it.
The Solution
In Looker, you can do this with a clever application of templated filters, as detailed in the steps below:
- First, hide the dimension in question using
hidden: yes
. This means the dimension will not show up in the Explore.
dimension: foo { type: string hidden: yes sql: ${TABLE}.foo ;; }
- Now, make a filter field. By itself, a field of
type: filter
does nothing, but combined with templated filters, you can link this filter to yourfoo
dimension.
filter: filter_on_foo { type: string sql: {% condition filter_on_foo %} ${foo} {% endcondition %} ;; }
- The
sql
parameter of a filter field applies SQL directly to theWHERE
clause of the query. In this case, the parameter is saying to take the filter condition specified in thefilter_on_foo
filter and apply it to the${foo}
dimension.
This way, you can just show users filter_on_foo
to let them filter on foo
while still keeping the foo
dimension hidden.