Looker: Accessing filter value in liquid IF condition

Question

I want to access the value of a filter on a dimension inside a liquid IF statement in a measure SQL line.

I know I can do this if my filter is a PARAMETER and I know I can do this if I'm using a derived table, but I specifically want to do this for a filter value of a regular filter on a dimension, in a regular SQL line for a measure. My goal is not achieving a certain calculation, but rather eliminating unnecessary case/whens in measures when the case/when is unneeded because of a specific filter value.

Here is my example:

I have a measure defined as follows:

  measure: distinct_users {
    type:  count_distinct
    sql:case when ${is_logged_in} then ${user_id} else NULL end;;
  }

This works great, but the case/when adds a lot of time. What I want to do is this: When I am filtering ${is_logged_in} to be true, the case/when is unneeded, so I want to use only sql: ${user_id}. That way this measure will be correct for any value of ${is_logged_in} and when ${is_logged_in} is filtered to be true, I can save time by getting rid of the case/when.

So I produced the following measure:

  measure: distinct_users_test {
    label: "No. of Registered Users"
    hidden: yes
    type:  count_distinct
    sql: {% if   is_logged_in._value =='true' %} ${user_id}  
               /* filtered  {% condition is_logged_in %}is_logged_in_condition{% endcondition %} 
                  {% parameter is_logged_in %} * {{ is_logged_in._value }} ** {{ is_logged_in }} */
         {% else %} case when ${is_logged_in} then ${user_id} else NULL end  
               /* not filtered  {% condition is_logged_in %}is_logged_in_condition{% endcondition %} 
                  {% parameter is_logged_in %} * {{ is_logged_in._value }} ** {{ is_logged_in }}  */   
         {% endif %} 
         /* _filters['is_logged_in']  */ ;;
    }

In addition to using a liquid IF to change the case/when to only ${user_id} when the filter was set to true, I added comments to show me how certain liquid statements were evaluated.

What I got was the following line in SQL for that measure, when is_logged_in is filtered to yes:

 COUNT(DISTINCT case when ( page_events.user_id > 0  ) then  "user_id"  else NULL end  /* not filtered  is_logged_in_condition true *  **   */    /* _filters['is_logged_in']  */ ) AS "page_events.distinct_users_test"

We see here the following:

1. The IF condition failed and did not succeed in removing the case/when
2. The filter value can be accessed in the SQL field using CONDITION or using {% parameter is_logged_in %} but these cannot be used in the liquid IF condition
3. The filter value cannot be access using ._value or {{ is_logged_in }}
4. The _filters['is_logged_in'] cannot work in a SQL field

In other words, the filter value IS available when the sql line is evaluated, but I can't use it in the IF statement!

I also tried is_logged_in._parameter_value but got a Looker error.

After talking to Looker support, I tried the following, which also did not work:

#    html: {% assign is_logged_in = _filters['page_events.is_logged_in']  %} {{rendered_value}} ;;
#    sql: {% if   is_logged_in =='true' %} ${user_id}  /* success  {% condition is_logged_in %}filter_true{% endcondition %} {% parameter is_logged_in %} * {{ is_logged_in._value }} ** {{ is_logged_in }} */     {% else %}case when ${is_logged_in} then ${user_id} else NULL end  /* failure  {% condition is_logged_in %}filter_true{% endcondition %} {% parameter is_logged_in %} * {{ is_logged_in._value }} ** {{ is_logged_in }} */    {% endif %}  ;;

The idea was that _filters['page_events.is_logged_in] should work in the HTML field, so I can assign a value to it and then use that value in the sql field, but it didn't work.

ANY SUGGESTIONS?????

I'm convinced that this must be possible, since the filter value is clearly available using {% parameter is_logged_in %} in the comment, but I can't find any way to use it in the IF.

Again, I know I can change the filter to a parameter and then use is_logged_in._parameter_value but that doesn't solve my real goal of using this in a lot of places to make resulting SQL faster by eliminating unnecessary case/whens.

ALL SUGGESTIONS VERY WELCOME! Thanks in advance!

Answer 1

I think you should use parameters instead of filters here. Also, you need to put the parameter value to compare in the "" as well as in the inner '', like this:

{% if   is_logged_in._parameter_value == "'true'" %} ${user_id} 

I use this method on my instance, and it works fine.