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

[sam8]

Knowledge Drop

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.                

[divya_bennett]

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