Power BI: How to 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:

Power BI distinct multiple columns

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 #1: In this example we extracted the distinct values from two columns but you can use similar syntax to extract distinct values from as many columns as you’d like by simply listing out more column names in the SUMMARIZE function.

Note #2: You can find the complete documentation for the SUMMARIZE function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

Power BI: How to Count Number of Occurrences in Column
Power BI: How to Count Distinct Values in Column
Power BI: How to Count Distinct Values with Filter

Leave a Reply

Your email address will not be published. Required fields are marked *