BUG: DAX DISTINCTCOUNT is not consistent with SQL; it counts NULL as a valid value

[ToddChitt]

You guys REALLY need to get on the same page as the SQL group in this regard.

A query in T-SQL for COUNT (DISTINCT <some column>) will NOT count NULL as one of the values.

In DAX, it will.

In MDX it will.

Is there not some ANSI standard that dictates what this should be?

If I was your boss I would lock both groups in a room and not let them out until they came up with a soltuion! And I would throw the MDX group in there as well.

I know, it's "by design". Well, it's a VERY POOR design then!

It's like this: There's an airplane flight manifest of names, and the flight has three empty seats. If you run a DISTINCTCOUNT of names assigned to all the seats, you would be off by ONE. If the plane crashed and the rescue workers asked the airline for the number of bodies they should look for, they would be looking for a long time to find that one NULL !

[v-qiuyu-msft]

Hi @ToddChitt,

Thanks for your feedback. You can vote this same idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20324836-dax-distinctcount-treats...

To workaround the issue currently, you can add a filter to exclude null values like below: 

Measure 3 = CALCULATE(DISTINCTCOUNT('testnull'[Category]),FILTER(ALL('testnull'),'testnull'[Category]<>BLANK()))

Best Regards,
Qiuyun Yu 

[ToddChitt]

@v-qiuyu-msftThanks for the comment. Did you see who the author was on the UserVoice item?

There have been no comments from anyone Microsoft on it. 

I see this as more of a system problem with Microsoft developers in general. The same issue applies to the IIF function between Reporting Services and Analysis Services. It is a clear indication of lack of coordinated effort between development groups, and duplication of core code.

I am not holding out to see it fixed as it is by now too far imbedded in everyone's models and by changing it now would cause major headaches for the consumers. It's just a thorn in my side everytime I need to do a DISTINCTCOUNT measure.

But Microsoft wrote the language, so THEY get to decide what is 'right'.

Maybe the next language they develop will have somthing like 1 + 1 = 3, and they'll call it 'by design'.

Just disappointed is all. 

[v-qiuyu-msft]

Hi @ToddChitt,

Thank you for your feedback again. I have consulted the PG team again, they replied below: 

This is by design for the existing DISTINCTCOUNT function. We’ll provide a separate function to count discount values minus the BLANK in the future.

Best Regards,
Qiuyun Yu