cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 this is by design or a bug. It feels like a bug to us.  Old feedback:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/20324836-dax-distinctcount-treats...

 

 

It is easy to duplicate.  On a local Sql server database create a table with a single column and a single null row of data. 

Note that count(distinct) of that table returns 0.

 

Now connect Power BI to that table (import or directquery - it doesn't matter). Show that column in a table or matrix and toggle to count (distinct).  Power BI shows 1.

 

It seems wrong that Power BI would work differently than SQL Server.  

 

Examples below...

 

sql server count distinct.PNGpower bi count distinct.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

@rlooney,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

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

 

 @v-yuezhe-msft - 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.

View solution in original post

4 REPLIES 4
Highlighted
Frequent Visitor

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

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.

 

 

Highlighted
Microsoft
Microsoft

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

@rlooney,


I will consult this issue internally and post back here once I get any updates.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

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

@rlooney,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

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

 

 @v-yuezhe-msft - 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.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors