Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

9 REPLIES 9
Thennarasu_R
Responsive Resident
Responsive Resident

Use instead of distinctcount to Max Inside if 

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

It doesnt work. Still same issue

YuvaSandiya
New Member

Hi Emily ,

 

Do you got the reply for the issue? I am also facing same issue.

 

Thank you

Sandiya

 

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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

 

Anonymous
Not applicable

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

 

Hi @Anonymous 

 

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

 

Anonymous
Not applicable

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

 

 

Hi @Anonymous 

 

Can you share the screenshot of your data model?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.