Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Advanced Templated Filters

Avatar
Tig Newman
  • Updated
Follow

This page assumes you understand the Templated Filters and Parameters page in Looker Docs. Be sure to check out the parameter discussion on that page, because using a parameter field is a better approach for many use cases.

The advanced templated filter applications discussed below are useful for a few use cases, such as making a derived table obey top-level filters for performance or aggregation reasons, creating dynamic filtered dimensions and measures, and creating dynamic WHERE clauses in queries.

Referencing a Dimension Instead of a Filter in Templated Filter Tags

In this example, we have a filter field to accept user input:

filter: my_filter_name { ... }

This filter dynamically modifies the derived table's SQL, like so:

{% condition my_filter_name %} ...

Alternatively, a dimension filter can also be referenced in a templated filter tag. For example, you can define a dimension:

dimension: my_dimension_name { ... }

And apply it to the derived table query the same way:

{% condition my_dimension_name %} ...

In this case, the normal dimension behavior occurs; an end-user applies a filter condition on the dimension from an Explore, dashboard or Look, and the underlying SQL is modified accordingly. The dimension's filter will also be applied anywhere the {% condition my_dimension_name %} templated filter tags are applied.

Applying a Templated Filter to a Dimension

In the example above, templated filters were used to dynamically modify the SQL of a derived table. Templated filters can also be used in the sql parameter of a dimension. For example:

filter: brand_select { ... }

dimension: brand_comparitor {
 sql:
 CASE
 WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
 THEN ${products.brand_name}
 ELSE "All Other Brands"
 END ;;
}

The result of this application is dynamic grouping, which is particularly useful for comparisons between an individual or set of items, compared to the rest of a population. Here the user leverages the brand_select filter to specify the brand they are interested in. The brand_comparitor dimension then groups the data into rows for that brand, versus rows for everything else.

Applying a Templated Filter to a Measure

You can also use Templated Filters in the sql of a measure. For example, you might have many item types and want to enable the user to retrieve a count for a specific type. You could create a count measure for each item type, but this would become unwieldy for more than a few types. It might also be the case that new types could potentially be added to the data in the future.

Using templated filters in a measure is a simple way to resolve this issue. Consider the following example:

filter: type_to_count { ... }

measure: type_count {
 type: sum
 sql:
 CASE
 WHEN {% condition type_to_count %} item.type {% endcondition %}
 THEN 1
 ELSE NULL
 END ;;
}

This application enables the user to select the desired type_to_count value, and then see the resulting count of that value in the type_count measure.

Using a sql Parameter in a Templated Filter

Templated Filters can also be used with a sql parameter. This applies secified conditions directly to the WHERE clause, whenever the filter has a value.

For example, suppose there is a customer table and a corresponding addresses table, where there can be more than one address per customer. If you only want to look at customers who have an address from a specific state, there are a few approaches to take:

  • Join addresses to customer, then filter on the address state.
    • However, doing so would result in a fanout, which is typically discouraged.
  • Create an address fact table, which has something like a list of states the customer lived in.
    • This might be a heavyweight solution for this one specific problem.
  • Use a correlated sub-query to determine customers who live in certain states, if your database dialect supports sub-queries. This would require a dimension for each specific state you were interested in. If the data for the dimension had more than a few possible values, this technique would quickly become unwieldy.

The simplest approach in this case, given the options, would be to use a templated filter. For example, below, we enable users to specify which state they are interested in:

filter: has_address_from_state {
 label: "FILTER Has Address from State"
 sql:
 EXISTS (
 SELECT *
 FROM addresses
 WHERE addresses.user_id = user.id AND
 {% condition %} state {% endcondition %}
 ) ;;
}

Note that in this case we have not specified a filter name in the {% condition %} tag. When you do this, Looker will assume you are referring to the filter where the tag is used.

Related articles