Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

5 REPLIES 5
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

can you post a sample of your dataset?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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. 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Anonymous
Not applicable

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?

Hi @Anonymous

 

why can you not unpivot it?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.