home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
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. A representative example of its usage is provided on the documentation page for column chart elements.

Usage

view: view_name {
  measure: field_name {
    filters: [dimension_name: "filter expression", dimension_name: "filter expression", …]
  }
}

Hierarchy

filters

Possible Field Types

Measure

Accepts

A set of dimension names and corresponding Looker filter expressions

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: [dimension_name_1: "Looker filter expression", dimension_name_2: "Looker filter expression", …]

Refer to Looker’s filter notation for details on how to write valid filter expressions. Place the entire filter expression in quotation marks, as shown in the examples below.

Examples

In this example, the measure will count distinct IDs that were created in the past seven 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: [created_date: "7 days", user.status: "-disabled"] # Reference fields from other joined views with view_name.field_name syntax # 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: [is_contractor: "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: [state: "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: [orders.price: ">100"] sql: ${orders.price} ;; }

In this example, the filter is given a range of numeric values. The measure will total orders from customers in the 18-25 age group:

measure: sales_18_to_25 { type: sum filters: [customers.age: ">=18 AND <=25"] sql: ${orders.price} ;; }

Common Challenges

Don’t Use filters with Measures of type: number

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

# Will NOT work measure: total_food_profit { type: number sql: ${total_revenue} - ${total_cost} ;; filters: [segment: "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 measure of type: number, 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: [segment: "food"] } measure: total_food_cost { type: sum sql: ${cost} ;; filters: [segment: "food"] }

Top