Creating a Filter-Only Field

The Problem

You want to create a dimension which cannot be selected in the UI, but you want to be able to filter on it.

The Solution

In Looker, this can be done with a clever application of templated filters, as you can see 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 your foo 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 the WHERE clause of the query. In this case, it’s saying:

take the filter condition specified in the filter_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.