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.
I have sql code I want to convert to dax, however, I'm getting different count result
The sql code when run will give 4936
Select count(distinct ItemNo) cnt from Item
Where Status = 'A'
AND (Depot = 'Vendor Delivers' OR Stock ='Direct')
The DAX, I wrote return 4922
Measure =
CALCULATE( DISTINCTCOUNT(Item[ItemNo])
,FILTER(Item,Item[Depot] ="Vendor Delivers"
|| Item[Stock] = "Direct" )
,FILTER(Item,Item[Status] = "A")
)
Solved! Go to Solution.
Here is a different way to write that. You should avoid filtering the whole table in your CALCULATE. Not sure this will give a different answer. However, you can troubleshoot your measure in the Data view on your table. Filter on the two columns for the desired values, and then select the ItemNo column to see the distinctcount below.
NewMeasure =
CALCULATE(
DISTINCTCOUNT( Item[ItemNo] ),
Item[Depot] IN { "Vendor Delivers", "Direct" },
Item[Status] = "A"
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a different way to write that. You should avoid filtering the whole table in your CALCULATE. Not sure this will give a different answer. However, you can troubleshoot your measure in the Data view on your table. Filter on the two columns for the desired values, and then select the ItemNo column to see the distinctcount below.
NewMeasure =
CALCULATE(
DISTINCTCOUNT( Item[ItemNo] ),
Item[Depot] IN { "Vendor Delivers", "Direct" },
Item[Status] = "A"
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat It didn't work, the Filter are from the same table but different columns Stock column and Depot column
Please check your dax
@Anonymous Try this also,
Measure =CALCULATE( DISTINCTCOUNT(Item[ItemNo]), Item[Depot] ="Vendor Delivers" || Item[Stock] = "Direct" || Item[Status] = "A")
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |