Forum Discussion
DAX - DISTINCTCOUNT or COUNT
Hi,
I have a table in PowerBI and would like to calculate the COUNT of ShipmentHouseBillId for all the DTD_Status.
ShipmentHouseBillId is a numerical value that can be duplicated across different DTD_Status. For example, ShipmentHouseBillId=1429 appeared in both of the "Late" and "On-Time" status.
I would like to calculate the DISTINCTCOUNT for ShipmentHouseBilllId regardless to the DTD_Status. In other words, I want the result below to display the counting = 5 instead of 4.
Already tried ALL, ALLEXCEPT, ALLSELECTED in DAX but seems not working, can anyone assist?
Thanks!
Best regards,
Emily
Hi , Anonymous
distinctcount: Counts the number of distinct values in a column.
count: Counts the number of cells in a column that contain non-blank values.Here is a demo
If help , try measures as below:
Total = COUNTROWS ( SUMMARIZE ( Range, Range[DTD_Status], Range[ShipmentHouseBillId] ) )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous ,
Try like
CALCULATE(DISTINCTCOUNT (Table[ShipmentHouseBilllId],all(Table)))Or
CALCULATE(DISTINCTCOUNT (Table[ShipmentHouseBilllId],allselected(Table)))amitchandak I just tried the below 2 formulas for ALL and ALLSELECTED in my case, but both of the result is still showing counting = 4 intead of 5.
Would you be able to further assist? Thanks.
Best regards,
Emily
Anonymous , Provide the data in table format. With expected output with that data.
