SQL to DAX

[Anonymous]

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")
    ) 

[mahoneypat · Accepted 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

[Anonymous]

@mahoneypat  It didn't work, the Filter are from the same table but different columns Stock column and Depot column

Please check your dax

[Tahreem24]

@Anonymous  Try this also,

Measure =CALCULATE( DISTINCTCOUNT(Item[ItemNo]), Item[Depot] ="Vendor Delivers" || Item[Stock] = "Direct" || Item[Status] = "A")