The Problem

I want to restrict a measure to only certain dimension values. In this example, I’ll create a measure that only shows my users from California.

The Solution

We can use the filters parameter of a measure to filter down a measure by a value.

In this example, I can write:

dimension: state {
    type: string
    sql: ${TABLE}.state ;;
  }

  measure: count {
    type: count
  }

  measure: count_california {
    type: count
    filters: {
      field: state
      value: "California"
    }
  }
  

With this, we can show the count of California users right beside the full count.

We can also take advantage of Looker’s filter syntax to build more robust measures, like a count of users not in California, or a count of users in states starting with the letter A.

measure: count_not_california {
    label: "Count of Users from States other than California"
    type: count
    filters: {
      field: state
      value: "-California"
    }
  }

  measure: count_states_a {
    type: count
    label: "Count of Users from States Beginning with A"
    filters: {
      field: state
      value: "A%"
    }
  }