User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
  
English
日本語
filters

This page refers to the filters parameter that is part of a measure.

filters can also be used as part of a native derived table (NDT), described on this documentation page.

filters can also be used as part of a dashboard, described on this documentation page.

filters can also be used as part of a dashboard element, described on this documentation page.

Usage

view: view_name {
  measure: field_name {
    filters: {
      field: dimension_name
      value: "looker filter expression"
    }
    # Possibly more filter statements
  }
}

Hierarchy

filters

Possible Field Types

Measure

Accepts

A pair of field and value parameters

Definition

filters is an optional list of filter expressions that are applied to a measure calculation. It only works with measure types that perform aggregation:

The syntax for filters is:

filters: { field: dimension_name value: "Looker filter expression" } # You can add another filters statement to add another filter

Refer to Looker’s filter notation for details on how to write valid filter expressions.

Examples

In this example, the measure will count distinct IDs that were created in the past 7 days, and were not from users with disabled accounts. This example also illustrates how to add multiple filters to a field. Each filter expression requires its own complete filters parameter:

measure: this_week_count { type: count_distinct sql: ${TABLE}.id ;; filters: { field: created_date value: "7 days" } filters: { field: user.status # Reference fields from other joined views with view_name.field_name syntax value: "-disabled" # Minus sign means "not" in this case, but check notation docs for details } }

As another example, consider data that includes one row per person, who is either a regular employee or a contractor. There is a field, is_contractor, of type yesno that contains yes if the person is a contractor. This measure will count only rows for contractors:

measure: contractor_count { type: count filters: { field: is_contractor value: "yes" } }

In this next example, the filters expression contains multiple values. The measure will count all rows that match any of the four values in the state field:

measure: customers_by_state { type: count filters: { field: state value: "California, Nevada, Washington, Oregon" } }

In this example, the filter is given a numeric value. The measure will add only orders that have a price of more than 100:

measure: total_amt_large_orders { type: sum filters: { field: orders.price value: ">100" } sql: ${orders.price} ;; }

Common Challenges

Don’t Use filters with type: number Measures

Many users try to use filters with type: number measures, which does not work:

# Will NOT work measure: total_food_profit { type: number sql: ${total_revenue} - ${total_cost} ;; filters: { field: segment value: "food" } } measure: total_revenue { type: sum sql: ${revenue} ;; } measure: total_cost { type: sum sql: ${cost} ;; }  

Instead, apply a filters parameter to any of the individual measures that make up the type: number measure, as follows:

# Will work measure: total_food_profit { type: number sql: ${total_food_revenue} - ${total_food_cost} ;; } measure: total_food_revenue { type: sum sql: ${revenue} ;; filters: { field: segment value: "food" } } measure: total_food_cost { type: sum sql: ${cost} ;; filters: { field: segment value: "food" } }

Top