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
- HotChilli
Community Champion
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?
- HotChilli
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.