Starting in Looker 21.8 you can add filters to custom measures natively in Explores without using Looker expressions when the with the Custom Fields Labs feature is enabled.

The Problem

I want to restrict a measure to aggregate only certain dimension values, without applying a filter to an entire query. In this article, we will address how to apply filter conditions to measure definitions, which will allow us to avoid filtering an entire query.

Applying Filter Conditions to Measures

To apply a filter directly to a measure, instead of filtering the entire query, we can add a filters parameter to the LookML definition of a measure. This will apply the filter, in the form of a CASE WHEN statement, to the measure in the generated SQL, as opposed to applying a global WHERE clause to the entire query.

As such, instead of removing rows from a query after it is aggregated, a filtered measure will only aggregate those rows that meet the specified conditions. This approach allows us to examine subsets of a population vs. other subsets or the whole.

For example, let's say we only want to see a count of records with a status of "cancelled".

  measure: cancelled_orders_last_week {
    type: count
    filters: [status: "cancelled"]
  }

We can also use Looker's filter expressions to achieve more nuanced filter conditions.

For example, the following filtered measure will produce a count of records that do not include a status of "cancelled":

    measure: cancelled_orders_last_week {
      type: count
      filters: [status: "-cancelled"]
    }

When multiple filters are defined, AND logic will be applied by default. Building off of the above example, let's say we want to see a count of records with a status of "cancelled," from last week specifically:

   measure: cancelled_orders_last_week {
     type: count
     filters: [status: "cancelled", date: "last week"]
   }

To achieve OR logic between filters, we can instead use a CASE WHEN statement in the sql parameter of our measure. For example:

  measure: count_CA_or_adult_users {
    type: count_distinct
    sql:
       CASE WHEN ${users.state} = 'California' OR ${users.age} > 18
       THEN ${user.id}
       ELSE NULL
       END ;;
  }

Dynamic Filtered Measures

In some cases, we may want filtered measures with a filter value that can change dynamically based on user input from a dashboard or Look. We can combine filtered measures with templated filters and parameters to make the filter value dynamic, based on front-end user input. This approach allows users to select filter values on the front end that only apply to a specific measure, without filtering the entire query in a global WHERE clause.

For example, to create a filter that gives users the option to see a count of orders, filtered by the status they choose:

First, create a filter-only field that allows the end user to select a value:

  filter: status_filter {
    type: string
    suggest_dimension: status
  }

Next, create a hidden dimension that leverages templated filters to capture whatever input the user selects from the above filter-only field:

  dimension: status_satisfies_filter {
    type: yesno
    hidden: yes
    sql: {% condition status_filter %} ${status} {% endcondition %} ;;
  }

This dimension will return yes> whenever a value is selected in filter field. Lastly, create a measure that filters on this type: yesno> dimension:
```
  measure: count_dynamic_status {
    type: count
    filters: [status_satisfies_filter: "yes"]
  }
    
```

If a user does not select a value in the filter-only field, the SQL will default to 1=1 (having no filtering effect on the measures).

Additionally, we can use Liquid parameters to filter measures, in order to directly insert user-inputted filter values into the SQL calculations of measures.

For example, let's say we want to compare a total of month-to-date spending with a sum of year-to-date spending side-by-side in one report. We also want for the end user to be able to select the date. To achieve this, we can create two measures that are filtered by these two different dynamic timeframes: month to [user-selected date] and year to [user-selected date]:

First, define a parameter of type: date that will allow a user to select a specific date:

  parameter: date_filter {
    type: date
  }

This will appear as a filter-only field in our Explore.

Next, create the two filtered measures that will display the different totals. We will plug the parameter date value chosen by the end user into each measure:

Note: Make sure to adjust the syntax for your SQL dialect

  measure: spend_year_to_selected_date {
    type: sum
    sql:
      CASE
        WHEN EXTRACT(YEAR FROM CAST({% parameter date_filter %} AS DATE)) = EXTRACT(YEAR FROM ${date_date})
        THEN ${TABLE}.spend
      END ;;
  }

  measure: spend_month_to_selected_date {
    type: sum
    sql:
      CASE
        WHEN EXTRACT(MONTH FROM CAST({% parameter date_filter %} AS DATE)) = EXTRACT(MONTH FROM ${date_date})
        AND EXTRACT(YEAR FROM CAST({% parameter date_filter %} AS DATE)) = EXTRACT(YEAR FROM ${date_date})
        THEN ${TABLE}.spend
      END ;;
  }

These measures will only aggregate the rows that fall within the specified date conditions; in this case, rows that are in the same month and year as the user-selected date inserted by our parameter.

The combination of filtered measures and Liquid filters offers incredible potential for flexible, dynamic filtering of specific aggregates in queries.

Articles in this section

Creating Filtered Measures

The Problem

Applying Filter Conditions to Measures

Dynamic Filtered Measures