Forum Discussion

rlooney's avatar
rlooney
Frequent Visitor
7 years ago
Solved

Count distinct of nulls in Power BI doesn't work like SQL Server

We had a customer run across this while validating data.     We see an idea in the old forum that indicates others have seen this problem as well, but I don't see any staff feedback to note that th...
Data Modeling
  • Anonymous's avatar
    Anonymous
    7 years ago

    rlooney,

    I have got response from PG that this behavior is by design, please vote up the idea you have mentioned above.

    Regards,
    Lydia

  • rlooney's avatar
    rlooney
    7 years ago

     

     Anonymous - Thank you for checking on this.  I have voted for the idea and this can be closed.

     

    Also, for others that might see this, one of the forum questions about this suggests adding a filter to the data to exclude the (blank) data.  Doing that will produce a count and count(distinct) that is consistent with SQL Server.  Just keep in mind what level you're filtering that data at as that might cause other problems if not fully accounted for.

rlooney's avatar
rlooney
Frequent Visitor
7 years ago

A couple more pieces of info -

 

1 - I can confirm that Cognos and Tableau both implement Count (distinct) like SQL Server does - ignoring nulls.  As a result, most users are going to expect count(distinct) to work in an industry standard way.

 

2 - Count also suffers from some oddities when a null value is in play. 

 

Count of the above table in SQL Server produces 0, but shows as a blank in Power BI which seems like another related bug.  

 

Adding a row of non-null data [insert into counttest (CountTestID) Values (1) ] then caauses this in Power BI:

 

Count = 1

Count (distinct) = 2

 

SQL Server produces:

 

Count = 1

Count (distinct) = 1

 

So Count is working when non-null values exist, but is broken when that is the only row of data.

 

Count (distinct) seems consistently to count the null value which other systems do not - and as a result, users won't expect.