Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
3) Right column => Count distinct of PurchaseOrderId column using the PowerBI default function
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).
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
Use instead of distinctcount to Max Inside if
It doesnt work. Still same issue
Hi Emily ,
Do you got the reply for the issue? I am also facing same issue.
Thank you
Sandiya
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] )
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?
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.
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 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |