Count unique distinct values in two columns

[Anonymous]

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

[LivioLanzo]

Hi @Anonymous

can you post a sample of your dataset?

[Anonymous]

Sure. Here is example from data. 

Work place A	Work place B	Work place C
- -	- -	Employee A
- -	Employee A	Employee 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 · Accepted solution]

@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]

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

date	machine	Work place A	Work place B	Work place C
1	x	- -	- -	Employee A
2	y	- -	Employee A	Employee B
3	x	Employee B	- -	Employee C
4	z	Employee A	- -	Employee C
5	y	Employee 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]

Hi @Anonymous

why can you not unpivot it?