cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Get distinct values of Multiple columns within the same table

Hi, Is there a way where I can combine distinct values of mutiple columns within the same table and display them in one single column.

 

For example I have Table A with columns X,Y,Z and all 3 columns have multiple values as shown below.

X        Y      Z

aa     ee    dd

bb    rrr    qq

ff      aa    rrr

 

The output of the DAX should be 

Outputcolumn

  aa

  bb

  ff

  ee

  rrr

  dd

  qq

Order of the values doesn't matter. 

 

Thanks for your time.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

@gwtham90 , Try a new table like

distinct(union(all(Table[X]),all(Table[Y]),all(Table[Z])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Super User III
Super User III

Hi,

In the Query Editor this should be easy to do.  Insert an Index column.  Select that column and "Univot other columns".  Remove all columns except the Value column.  Remove duplicates from the Value column.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User IV
Super User IV

@gwtham90 , Try a new table like

distinct(union(all(Table[X]),all(Table[Y]),all(Table[Z])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors