Question

# Discrepancy when drilling into measures

• 1294 views

Userlevel 3
• Looker Staff
• 45 replies

If a measure is being drilled into and outputting incorrect information, this could be due to a `CASE WHEN` statement in your measure.

Let’s say we want a measure that counts how many companies have exactly 13 employees. We could create a measure that includes a `CASE WHEN` statement in the sql parameter. For example,

New LookML

``````measure: count_employees_thirteen {
type: count_distinct
sql: CASE WHEN \${number_of_employees} = 13 THEN \${company_id}
ELSE NULL
END ;;
drill_fields: [companies.employees*]
}
``````

Old LookML

``````  - measure: count_employees_thirteen
type: count_distinct
sql: |
CASE WHEN \${number_of_employees} = 13 THEN \${company_id}
ELSE NULL
END
drill_fields: [companies.employees*]
``````

This measure would give us the correct count but the drill modal would be incorrect.

Shouldn’t the number of employees in my drill ONLY be 13?

This happens because the `CASE WHEN` statement is converting Company ID’s that don’t match the condition to null and then counting non-null Company ID’s. Thus, our `CASE WHEN` statement is not actually filtering any values. So how do we get around this? We can create a `yesno` dimension that replaces our condition and then filter the measure on that dimension.

For example,

New LookML

``````dimension: thirteen_employees {
type: yesno
sql: \${number_of_employees} = 13 ;;
}

measure: count_employees_thirteen {
type: count
filters: {
field: thirteen_employee
value: "yes"
}
drill_fields: [companies.employees*]
}
``````

Old LookML

``````  - dimension: thirteen_employees
type: yesno
sql: \${number_of_employees} = 13

- measure: count_employees_thirteen
type: count
filters:
thirteen_employees: TRUE
drill_fields: [companies.employees*]
``````

The filter is now applying to the drill.

This is a good pattern when filtering on a measure and looking to pass that filter onto the drill modal.