Question

Discrepancy when drilling into measures

  • 28 September 2016
  • 1 reply
  • 1193 views

Userlevel 3

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.

1 reply

Userlevel 7
Badge +1

2 posts were split to a new topic: OR logic between filters in explore?

Reply