Forum Discussion

msit19's avatar
msit19
Frequent Visitor
6 years ago
Solved

How Convert this Sql to Dax

welcome Can you help me to convert this instruction to Dax   select count(distinct(CustomerCode)) from Tab1 where Fld1=@Var1 and CustomerCode not in(select distinct(CUSTOMER_NO) from Tab2 where F...
  • amitchandak's avatar
    amitchandak
    6 years ago

    msit19 , Try like

    meausre =
    calculate(distinctcount(Tab1[CustomerCode]) , filter(Tab1, not( CustomerCode in values(Tab2[CUSTOMER_NO]))))

     

    meausre =
    calculate(distinctcount(Tab1[CustomerCode]) , filter(Tab1, not( CustomerCode in allselected(Tab2[CUSTOMER_NO]))))

     

    Assuming Fld1 from both tables is joined to common dimension and that dimension is used as a slicer to filer values

     

    Another option in place of in is treatas

    https://docs.microsoft.com/en-us/dax/treatas-function

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

msit19 , Try like

meausre =
calculate(distinctcount(Tab1[CustomerCode]) , filter(Tab1, not( CustomerCode in values(Tab2[CUSTOMER_NO]))))

 

meausre =
calculate(distinctcount(Tab1[CustomerCode]) , filter(Tab1, not( CustomerCode in allselected(Tab2[CUSTOMER_NO]))))

 

Assuming Fld1 from both tables is joined to common dimension and that dimension is used as a slicer to filer values

 

Another option in place of in is treatas

https://docs.microsoft.com/en-us/dax/treatas-function

  • msit19's avatar
    msit19
    Frequent Visitor
    6 years ago

    thanks for your reply I don’t want to add a condition to the second table  How ?


    select count(distinct(CustomerCode)) from Tab1 where Fld1=@Var1 and CustomerCode not in(select distinct(CUSTOMER_NO) from Tab2 where Fld1=2000)