Todd Chittenden on 28 Jul 2017 21:38:25
I see this as a BUG. DAX function DISTINCTCOUNT considers NULL a valid value and will count it.
THIS IS CONTRARY to what you would see in the SQL Database engine for COUNT ( DISTINCT ).
Is there not some kind of ANSI standard on what this should be?
Cross reference this Connect item concerning the same issue in Analysis Services: https://connect.microsoft.com/SQLServer/feedback/details/568056/distinct-count-measure-returning-1-analysis-services
Honestly, it's like you have two different groups coding things two different ways.
Same is true for IIF () function. In Reporting Services, it will evaluate BOTH True part and False part BEFORE it decides which path to take. But in Analysis Services it figures out which path to take FIRST.
- Comments (7)
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
dunno if what you posted was out of date but it should be
Green Count = DISTINCTCOUNT(PopulationTable[GREEN])-(IF(COUNTBLANK(PopulationTable[GREEN])>0,1,0))
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
Totally agree. We use this function frequently and it's not nice to use DAX every time. Hope it could be fixed soon.
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
We're often comparing results from SQL Server reports to those in PBI and this has caused headaches for us when we couldn't figure out why counts were one off each time. It has also resulted in reports where where we had conflicting counts of the same data on a dashboard because of this (one visual was filtering out the nulls).
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
MS states here that the inconsistent functionality of "Count" and "Count(Distinct)" as compared to SQL Server and other BI systems with null data is by design.
https://community.powerbi.com/t5/Issues/Count-distinct-of-nulls-in-Power-BI-doesn-t-work-like-SQL-Server/idc-p/568940#M35099
I guess this becomes a request to add new Aggregate functions for "Count [no nulls]" and "Count (Distinct) [no nulls]" or some other option to exclude nulls from the built in count functions when setting aggregates.
Anyone validating data from SQL Server to Power BI using count functions will encounter this issue.
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
Just ran across this bug (and it IS a bug) and agree completely. Now I have to create calculated columns for every field I need to count...
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
I couldn't agree more, if you want to count blank ok fine, but distinct count of null should be 0 not 1. Handle it the way SQL would..
RE: DAX DISTINCTCOUNT treats NULL as a DISTINCT value
I agree with you.
This is wrong behavior.
That was the contouring solution that I implemented.
TOT = DISTINCTCOUNT(AcessoAcordo[Lojas Visitadas])-(IF(COUNTBLANK(AcessoAcordo[Lojas Visitadas])>1;1;0))