Knowledge Drop

Why do I have more rows in my drill table than my count_distinct measure indicates?

  • 5 April 2021
  • 1 reply
  • 537 views

Userlevel 5
Badge
  • Looker Staff
  • 172 replies

Last Tested: March 15, 2021

 

If multiple rows have the same value in the column which is being aggregated with a count distinct, you could see this behavior. The drill will show distinct combinations of the dimensions you've indicated in that measure's drill_fields parameter, which might be more numerous than the distinct values counted by your count distinct aggregation.

Example:

If you have a count distinct on ID that shows 1 in the column, and if more fields are selected in the drill down, you could have this:

id value
1 A
1 B
1 C

There is 1 distinct ID, but is getting expanded in the drill.

Also, in cases where count is being filtered using a CASE WHEN statement in the sql parameter of the field you're trying to drill into, this CASE WHEN condition doesn't get factored in to the drill filters, which can also cause a discrepancy. This Community post describes using a filtered measure instead of a CASE WHEN to resolve this issue.
 

 

This content is subject to limited support.                

 

 


1 reply

So, what is the solution? I havent used a CASE...WHEN stt. How do I set drills for count_distinct values without getting duplicates?

Reply