View the original community article here
Last tested: Nov 19, 2019
The Problem
You want to allow their users to implement OR logic between two separate filters (e.g. Age >= 25 OR State="New York") but this is not possible using Looker's default filter options. Usually we'll recommend using custom filters to get around this however these are not available on dashboards and are not always business user friendly.
A Solution
We can use templated filters and liquid parameters in a sql_always_where
parameter on the Explore to get around these limitations. This will ask a bit more of developers but will ultimately allow them to provide a better solution for their front-end users.
- Create filter-only fields for the fields that you want users to be able filter with OR logic.
filter: state_filter {
type: string
suggest_dimension: state
}
filter: age_filter {
type: number
}
- Create a liquid parameter to allow users to input the filter logic of choice.
parameter: filter_logic {
type: unquoted
allowed_value: {
label: "OR"
value: "OR"
}
allowed_value: {
label: "AND"
value: "AND"
}
}
- Add a
sql_always_where
to your explore where you will apply the templated filters and liquid parameter from steps 1 & 2.
explore: users {
sql_always_where:
{% condition users.state_filter %} users.state {% endcondition %}
{% parameter users.filter_logic %}
{% condition users.age_filter %} users.age {% endcondition %};;
}
What you're doing here is applying the values and filter logic selected by the front end user to your two filter fields (state_filter
and age_filter
), applying those to the underlying fields you want to update (state
and age
), and separating them by the filter_logic
(OR or AND) selected.
From there I'd recommend adding an always_filter
on the Explore or using these filters at the dashboard-level for the smoothest end-user experience but otherwise you should be good to go!
This content is subject to limited support.