Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 (...
Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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's avatar
mahoneypat
Microsoft Employee
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Tahreem24
      Super User
      4 years ago

      Anonymous  Try this also,

       

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