Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello Power BI community,
I have been trying for some time now to figure out the solution to this problem (I am new to Power BI and DAX) but I could not find the solution myself or on the internet. The problem is pretty basic:
I have a table and I would like to extract the unique values from some of the columns. For example, from the following table I would like to have the unique values of columns 1, 2 and 3, so the answer would be A, B, C, D.
A | D | C | 1 |
A | C | A | 2 |
B | A | B | 3 |
C | B | C | 4 |
D | B | A | 5 |
D | B | B | 6 |
Thanks in advance (also for redirecting me to relevant posts that I might have missed)!
Hi,
I had a similar need, so i used the following steps:
Created a new table using DAX UNION and DAX SELECTCOLUMNS,
New_table_name=
UNION(
SELECTCOLUMNS(source_table_name,"new_column_name",source_column_name1),
SELECTCOLUMNS(source_table_name,"new_column_name",source_column_name2),
SELECTCOLUMNS(source_table_name,"new_column_name",source_column_name3),
.
.
.
)
this will create a new table, with summarized columns.
On top of this use DAX Measure , DISTINCTCOUNT or COUNTROWS(FILTER(table_name,NOT(ISBLANK(column_name))
This measure will help you to get a pivot table kind of result with individual columns.
From This
to This:
Hello @BI21 ,
like for example you could in power query, duplicate the table you have then remove all columns except the one you want to extract the values from then right click on this column and choose remove duplicates.
so you'll have every unique value in this column.
If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍
Proud to be a Super User! | |
Thank you for your hint! However, this only works when I apply it to one of the columns, not to several columns. So when I mark the first two columns of the example data and apply "Remove duplicates", it only removes the last row because the entry "D (in column 1) B (in column2)" is indeed a duplicate but the result is still a table. I want as answer only "A, B, C, D"
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
74 | |
57 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |