Forum Discussion
SQL to DAX
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")
)
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
3 Replies
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
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")
