Table of Contents
Power BI is a powerful data analytics tool that allows users to gather insights and make informed decisions based on their data. One of its key features is the ability to obtain unique values from multiple columns, which can be useful in identifying patterns and trends within a dataset. This can be done by using the “Distinct” function in the Power BI Query Editor, which allows users to select and extract only unique values from selected columns. By utilizing this functionality, users can efficiently analyze their data and gain a deeper understanding of their business or organization.
Power BI: Get Distinct Values from Multiple Columns
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: