Dynamic Filter in LookML

[JVFrancis]

I am trying to create a dynamic filter to use in a dashboard. Ideally when the user selects a value from the Filter Selector filter the values in the Dynamic Filter filter would change accordingly. For example, if the value "Age" is selected from the Filter Selector filter the value in the Dynamic Filter filter would be 10,20,30 etc, similarly when another value is selected in the Filter Selector filter the values in the Dynamic Filter filter should change accordingly. Below is my LookML,

parameter: filter_selector {
type: string
default_value: "Age"
allowed_value: {
label: "Applicant Age"
value: "Age"
}
allowed_value: {
label: "Applicant Sex"
value: "Sex"
}
allowed_value: {
label: "Applicant Race"
value: "Race"
}
}

dimension: dynamic_filter {
label_from_parameter: filter_selector
sql:
{% if filter_selector._parameter_value == "Age" %} ${Age}
{% elsif filter_selector._parameter_value == "Sex" %} ${Sex}
{% else %} ${Race}
{% endif %};;
}

When I select the "Age" value in the Filter Selector filter I am seeing the values for "Race" in the Dynamic Filter filter instead of the values for "Age" . The appropriate values (based on the value selected in Filter Selector filter) are not showing up in the Dynamic Filter.

No sure where I am messing up things in my LookML code. Can someone please help?

[Zach_A · Accepted solution]

Hi JVFrancis,

First you'll need to make a slight change to how you're utilizing the parameter in your dynamic dimension - modify it to use this syntax for referencing the parameter values:

parameter: dimension_selector {
    type: unquoted
    default_value: "Age"
    allowed_value: {
      label: "Age"
      value: "age"
    }
    allowed_value: {
      label: "Gender"
      value: "gender"
    }
  }
  
  dimension: dynamic_dimension {
    type: string
    sql: {% if dimension_selector._parameter_value == "age" %} ${age_tier}
      {% elsif dimension_selector._parameter_value == "gender" %} ${gender}
      {% else %} ${age_tier}
      {% endif %} ;;
  }

 In order to use these fields - you'll want to set your dashboard up like this:

You'll also need to modify your parameter filter in the dashboard to "Select filters to update when the filter changes" so that the suggestions reload when you change the parameter value:

Last note is that you'll need to make sure that "Age" is a string so that suggestions are actually shown in the filter - number type dimensions will not have suggestions. 

[sonalch]

Hi, I’m building a Looker dashboard where I want certain filters to appear dynamically based on the selection of a hospital. Specifically:

• If hospital = "New York", then I should see filters for Region and Street.
• If hospital = "London", then I should see filters for Speciality and Postcode, while hiding Region and Street.

Please let me know if this is achievable using Looker. Thanks in advance !

[JVFrancis]

Thanks a lot @Zach_A Much appreciated! Works like a charm!

I have a quick question regarding your comment Last note is that you'll need to make sure that "Age" is a string so that suggestions are actually shown in the filter - number type dimensions will not have suggestions. 

What can I do if the value in the dimension_selector parameter is of a number or datetime datatype other than string datatype? For example in my case I would want to use the field  "Date Of Birth" which is of type: time & datatype: datetime as a part of the parameter dimension_selector and dimension: dynamic_dimension. Below is the LookML of the "Date OfBirth"  dimension_group

dimension_group: DateOfBirth {
type: time
datatype: datetime
label: "Date Of Birth"
timeframes: [raw,time,date,week,month,quarter,year]
sql: ${TABLE}.DateOfBirth ;;
}

[Zach_A]

You could still use those kind of dimensions but there wouldn't be any suggestions popping up. For date fields, the filter would be a date selector. 

For Date of Birth - do you want users to select one single specific date or would you want them to select a range of days? 

[JVFrancis]

@Zach_A For Date Of Birth, I would like the users to select a range of days like how other date filters work on a dashboard like the screenshot below,

And I would want the Date Of Birth to be a part of the same parameter. Is that possible?