Question

Discrepancy when drilling into measures

  • 28 September 2016
  • 1 reply
  • 1294 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