Faceted Filters in Explores (workaround)

Knowledge Drop

Last tested: Nov 26, 2020
 

Faceted (linked) filters in explores aren't possible in Looker. Be sure to give a +1 to the feature request

Situation:

Let's say we two have two filters on an explore page: Country and State. We want the State filter to populate based on the Country filter's selection.

Steps:

  1. Add a User Attribute for Country.
  2. Create a derived table for State by using the following syntax:

view: suggestions {

derived_table: {

sql: select distinct state

from your_schema_name.your_table_name

where

country = {{ _user_attributes['country'] | | prepend: "'" | append: "'" }} ;;

}



dimension: state {

type: string

sql: ${TABLE}.state ;;

}

}

  1. On the explore page, apply the Country filter normally, but make sure to apply the State filter based on the dimension from the derived table, not from the original data table.
  2. Change User Attribute value to be whatever you select in the Country filter on the explore.

This is a more involved workaround and would not scale well if you have many faceted filters on your explore page. It would also require adding user attributes for each filter field.

This content is subject to limited support.                

Comments
mjanssen
New Member

Hi Evelyn,

I am curious to know how you would change a user attribute to whatever you select in the country filter (number 4 of your explanation). Not sure how to do that.

Thanks,

Mark

evelyn_shen
Staff

The user attribute value can be changed using the steps in https://docs.looker.com/admin-options/settings/user-attributes#assigning_values_to_individual_users.

As an example, if you want to show states in the United States, you would select “USA” from the Country filter on the explore UI but would also need to change the user attribute value to “USA”. Note that only admin users can edit user attributes.

This workaround is very much a workaround and as you can see, it would only work in situations where you would not need to change the country selected very often. 

mjanssen
New Member

Thanks Evelyn for your prompt answer.
I was hoping for some automated solution to change the user attribute.
Any idea if this feature is coming in one of the next versions ?

evelyn_shen
Staff

It’s possible to update the value of a user attribute through the update_user_attribute API endpoint. Depending on how savvy you are with custom API scripts, I envision that it’s possible to write a script to update the value of a user attribute based on some action in the UI.

As far as I know, there isn’t a UI-based feature that will allow user attribute values to update automatically, nor is there one in the near-term product roadmap. A custom script would be the best bet, though definitely quite a bit of upfront work! 

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: