Forum Discussion

tortugamarina's avatar
tortugamarina
Frequent Visitor
5 years ago

DAX - Measure to count distinct values when countrows with same ID is 2

Hi all,   I am trying to calculate: 1) Number of Departments where each customer has bought 2) For those customers who bought in 2 different departments, what is the total count of those departme...
Need Help
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This measure works

Measure = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[customerID]),Data[customerID],"ABCD",COUNTROWS(Data),"EFGH",CALCULATE(COUNTROWS(Data),ALL(Data[Department]))),[ABCD]>0&&[EFGH]=2))

Hope this helps.

  • tortugamarina's avatar
    tortugamarina
    Frequent Visitor
    5 years ago

    Hi,

    This solution is not working for me. I get totally random values that does not make sense. Could you please explain a bit the DAX and name ABCD and EFGH in a way that I can understand what they calculate?

     

    Thank you.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      I do not what the reason could be.  You can very clearly see in my screenshot that it works fine on the data that you had shared.

      • tortugamarina's avatar
        tortugamarina
        Frequent Visitor
        5 years ago

        Thanks for your explanation Ashish.