Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count unique distinct values in two columns

Hi   I am trying to build a measure which counts unique value from two columns. This is perfect explanation what i want to do: Excel example. How i can similar get result with DAX?   Thank you
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Anonymous

     

    you can do it like this, but its most likely better to unpivot these 3 columns into 1 column and it gets much easier then

     

    = 
COUNTROWS(
    DISTINCT(
        FILTER(
            UNION(
                ALLNOBLANKROW( Data[Work place A] ),
                ALLNOBLANKROW( Data[Work place B] ),
                ALLNOBLANKROW( Data[Work place C] )
            ),
            [Work place A] <> ""
        )
    )
)
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Hi Anonymous

 

can you post a sample of your dataset?

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sure. Here is example from data. 

 

Work place AWork place BWork place C
- -- -Employee A
- -Employee AEmployee B
Employee B- -Employee C
Employee A- -Employee C
Employee B- -Employee D

 

And I want result: Unique distinct count 4 . Which is number of different employees from all the work places. 

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Anonymous

     

    you can do it like this, but its most likely better to unpivot these 3 columns into 1 column and it gets much easier then

     

    = 
COUNTROWS(
    DISTINCT(
        FILTER(
            UNION(
                ALLNOBLANKROW( Data[Work place A] ),
                ALLNOBLANKROW( Data[Work place B] ),
                ALLNOBLANKROW( Data[Work place C] )
            ),
            [Work place A] <> ""
        )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you LivioLanzo for help. I can't unpivot the data. So could you help more with making the measure.

       

      datemachineWork place AWork place BWork place C
      1x- -- -Employee A
      2y- -Employee AEmployee B
      3xEmployee B- -Employee C
      4zEmployee A- -Employee C
      5yEmployee B- -Employee D


      There is also date and machine information. I want use them with slicer. Does it effect to making measure? 

       

      EDIT: How to make this code response to date and machine slicer?

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        Hi Anonymous

         

        why can you not unpivot it?