Discrepancy when drilling into measures

[paola1]

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.