Forum Discussion
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
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] <> "" ) ) )
Hi Anonymous
can you post a sample of your dataset?
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.
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] <> "" ) ) )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?