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

Wrong Count Distinct result in Power BI

Hi,

 

I want to simply calculate the total number of PurchaseOrderId in Power BI Desktop as per my below example, but the result gives me a wrong count.

 

Here is the settings in the below table:

1) Left column => PurchaseOrderId

 

2) Middle column => Count distinct of PurchaseOrderId column using DAX formula:

IF(DISTINCTCOUNT([PurchaseOrderId])=BLANK(),0,
DISTINCTCOUNT([PurchaseOrderId]))

 

3) Right column => Count distinct of PurchaseOrderId column using the PowerBI default function

1.PNG

 

The count result in the table does not give the right answer, as you can see that some of the records cannot be counted properly (eg. PurchaseOrderId = 38, 47, 48......etc)

 

If I check the PurchaseOrderId column from the database, it gives me a much higher result (Count distinct of PurchaseOrderId should be 7357 instead of 4409 shown in Power BI).

2.PNG

 

I already tried a lot of workarounds but all of them were unsuccessful (eg. add a new column into the model and count & sum based on that column).

It seems the counting function in Power BI is not working because the result does not match with the database, and I don't know why some of the PurchaseOrderId cannot be counted properly (displayed either 0 or blank in the middle and right column).

Not sure if this is a bug in Power BI or not......can someone help and advise on this?

Thanks!

 

Best regards,

Emily

 

6 REPLIES 6
Super User IV
Super User IV

Re: Wrong Count Distinct result in Power BI

Hi @emilypoon1 

 

The following measure should work as long as you use the right fields.

PurchaseOrderId filed in the table visual should be the same as the one used in the measure ( comming from the same table ).

Measure = DISTINCTCOUNT( 'OrderedItemSummary'[PurchaseOrderId] )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

emilypoon1 Frequent Visitor
Frequent Visitor

Re: Wrong Count Distinct result in Power BI

Hi Mariusz,

 

I already tried your solution but that still gave me a wrong result as before (see last column "Measure" in the below table.

Any other ways on how to solve this problem?

 

3.PNG

 

Best regards,

Emily

 

Super User IV
Super User IV

Re: Wrong Count Distinct result in Power BI

Hi @emilypoon1 

 

Please check if you are not applying any filters ( page level or all pages ).
If you are prepping data in Query Editor check if you have not left any filters there.

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

emilypoon1 Frequent Visitor
Frequent Visitor

Re: Wrong Count Distinct result in Power BI

Hi Mariusz,

 

Yes I already checked the filters at visual/page level but none of them applied to the page & visuals that I am working with. Same for query editor with no pre-filters applied.

 

Best regards,

Emily

 

 

Super User IV
Super User IV

Re: Wrong Count Distinct result in Power BI

Hi @emilypoon1 

 

Can you share the screenshot of your data model?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

emilypoon1 Frequent Visitor
Frequent Visitor

Re: Wrong Count Distinct result in Power BI

Hi Mariusz,

 

Sorry that I can't share the data model to you due to client's data confidentiality.

Actually my model is pretty simple - just 1 data table that contains "PurchaseOrderId" column along with other columns.

Primary Key for the table is "Id" and the "PurchaseOrderId" is one of the Foreign Key.

What I am trying to do is just a simple summation of "Count Distinct of PurchaseOrderId", not sure why some of the PurchaseOrderId is countable in PowerBI while some of them are not countable.

 

Best regards,

Emily

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors