A filter that depends on the parameter

[carlosavendano]

Hi everyone

We would like to have a filter that depends on the value of a parameter, for example we create a parameter with 3 possible values, and depending on the value that the user selects then there is a different filter applied.

For example, a parameter that let you decide between a dimension of status,  country and state. We want that if the user selects the "status" dimension via this parameter, in that case there will be a filter applied for example ='Done', if they select the "country" dimension via this parameter there will be a filter like In ('UK','France'), and if they select the state dimension there will be no filter at all.

Is it possible? 

Thanks in advance for your help 

[francois_care]

Hello @carlosavendano,
Thank you for your message.

Yes, a version of this should be possible with LookML parameter and liquid language, more info here.
As per the example of the working code below, I believe you would first need to create the filter selector and then create a "dynamic" dimension based on the parameter value (example of code below)

parameter: filter_selector {
    label: "Choose a filter"
    view_label: "01. Dynamic Analysis"
    type: unquoted
    default_value: "1"
    
    allowed_value: {
      label: "Status"
      value: "1"
    }
    allowed_value: {
      label: "Country"
      value: "2"
    }
    allowed_value: {
      label: "State"
      value: "3"
    }
  }
  
  # IMPORTANT - replace the {( )} in the html section below with double { curly brackets (not appreciated in our Google Community)
  dimension: dynamic_column {
    label: "Dynamic filter"
    view_label: "01. Dynamic Analysis"
    label_from_parameter: filter_selector
    full_suggestions: yes
    type: number
    sql:
          {% if filter_selector._parameter_value == '1' %} ${status_dimension}
          {% elsif filter_selector._parameter_value == '2' %} ${country_dimension}
          {% elsif filter_selector._parameter_value == '3' %} ${state_dimension}
          {% else %}  ${status_dimension}
          {% endif %}
        ;;
    html:
          {% if filter_selector._parameter_value == '1' %} {(status_dimension)}
          {% elsif filter_selector._parameter_value == '2' %} {(country_dimension)}
          {% elsif filter_selector._parameter_value == '3' %} {(state_dimension)}
          {% else %}  {(status_dimension)}
          {% endif %}
        ;;
  }

This way you would indirectly filter your data with a dynamic column.
To make it a filter directly, it is more tricky as liquid parameters do not work well in filter queries.
I'll try to think of an approach and circle back to you here.

Happy Looker modelling ! 🙂

[carlosavendano]

Hi Francois,

Thank you for your answer, but Im not sure if it solves our issue:

In your code there, you have created a parameter, but it does not filter the result. For example, if a user selects '2' in the parameter, the dynamic dimension will get the value of the Country dimension, but this is how a regular parameter works. We would need also a specific filter depending on the parameter, so if the user selects '2', not only it defines the dimension of the dynamic dimension but it also generates a specific filter, for example, that country would be in ('UK,'France'). And if you select another value of the parameter, will change not just the dynamic dimension but also the filter. Would it be possible? Also im not really sure HTML there, since it has the same code as the sql, is it necessary?

thanks in advance,

best regards

[francois_care]

Hello @carlosavendano,
Yes, what you are trying to do is tricky as there is no SQL queries being normally run upon updating a filter (in the Explore layer) and those may crash if you use liquid parameters.

After trying a few things, I however made it work by simply connecting your dynamic column to your parameter filter within the dashboarding layer as per the picture below,

If you are using Looker externally, using javascript might also be a more flexible approach to consider 🙂

[carlosavendano]

That could work indeed, I will try it during today! Thank you for your help