Advanced Templated Filters

Looker will not be updating this content, nor guarantees that everything is up-to-date. 


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.

Comments
Pinkone
New Member

Hi there, can we apply this technique to an Explore (like in a join condition)?

Update: just tested it, yes we can!

Version history
Last update:
‎05-17-2022 09:36 AM
Updated by: