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

5 Replies

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