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.

1. 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

}

2. 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"

}

}

3. 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!