Table of Contents
To retrieve distinct values from multiple columns using Power BI, you can use the “Remove Duplicates” feature. This allows you to select the columns you want to analyze and then removes any duplicate values, leaving only unique values. Additionally, you can use the “Group By” feature to group your data by a specific column and then select the “Distinct Count” option to see the unique values within that group. This allows you to get a comprehensive view of the distinct values across multiple columns in your data set.
You can use the following syntax in DAX to create a new table that contains only the distinct values across multiple columns in a specific table in Power BI:
Distinct Table = SUMMARIZE(my_data, [Team], [Position])
This particular example creates a new table named Distinct Table that contains only the distinct combination of values across the Team and Position columns of the my_data table.
The following example shows how to use this formula in practice.
Example: How to Get Distinct Values from Multiple Columns in Power BI
Suppose we have the following table in Power BI named my_data that contains information about basketball players on various teams:
Suppose that we would like to create a new table that extracts the distinct values across the Team and Position columns.
To do so, click the Table tools tab along the top ribbon, then click the New table icon:
Then type the following formula into the formula bar:
Distinct Table = SUMMARIZE(my_data, [Team], [Position])
This will create a new table named Distinct Table that contains only the distinct values across the Team and Position columns of the my_data table:
For example, there were multiple rows in the original my_data table that had a Team value of “A” and a Position value of “Guard” but only one row in this new table contains this combination of values.
Similarly, there were multiple rows in the original my_data table that had a Team value of “A” and a Position value of “Forward” but only one row in this new table contains this combination of values.
And so on.
Note #2: You can find the complete documentation for the SUMMARIZE function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: