Forum Discussion
DISTINCTCOUNT calculates wrong with filter
Guys!
I'm having some difficulty in the filter context for the DISTINCTCOUNT expression. When I apply a filter to the measurements, it presents incorrect data, but when I use SUM or COUNT, the data is presented correctly. Could anyone tell me why it doesn't work just for DISTINCTCOUNT.
If I add the distinct counts, I don't get the total of 2,523 but rather 2,582. This difference only happens when I use the DISTINCTCOUNT expression. If you look at the second line of data, the sum is correct at 8,070, which I used COUNT.
Download file .pbix:
https://drive.google.com/file/d/1k9jMKQ_EFLTmJOpQAzAQdfAp3ipzgWSN/view?usp=sharing
Thanks for the pbix.
Can you explain a bit more about what we need to investigate? I see 2 rows of visuals. Where is the error or wrong data displayed?
3 Replies
Thanks for the pbix.
Can you explain a bit more about what we need to investigate? I see 2 rows of visuals. Where is the error or wrong data displayed?
There are 2523 distinct values in the whole dataset for COD. There is overlap of COD in the different Filter categories.
One example is for COD = 106. There are rows for Filter = LT and rows for Filter = Na e LT.
So these values will be included in the distinctcount for [Total Count LT] and [Total Count NA e LT]
but they will only count as 1 in the [Total].
So the [Total] measure is accurate and the individual distinctcount measures are also accurate but the [Total] will be less
than adding up the individual measures.