topic DISTINCTCOUNT calculates wrong with filter in DAX Commands and Tips

DISTINCTCOUNT calculates wrong with filter

Anonymous — Wed, 12 Jun 2024 16:48:43 GMT

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

Re: DISTINCTCOUNT calculates wrong with filter

HotChilli — Wed, 12 Jun 2024 14:58:43 GMT

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?

Re: DISTINCTCOUNT calculates wrong with filter

Anonymous — Wed, 12 Jun 2024 16:48:08 GMT

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.

Re: DISTINCTCOUNT calculates wrong with filter

HotChilli — Wed, 12 Jun 2024 21:57:51 GMT

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.