Advanced Templated Filters

  • 17 May 2022
  • 1 reply
  • 1704 views

  • Looker Staff
  • 37 replies

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.


1 reply

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

Update: just tested it, yes we can!

Reply