How to Get Unique Values from Multiple Columns in Power BI

Leveraging DAX for Dynamic Distinct Sets

While the transformation features available in Power Query are excellent for cleaning source data, often a requirement arises to create a dynamic table containing unique combinations directly within the Power BI data model. This is where DAX (Data Analysis Expressions) becomes indispensable. By using DAX to define a new calculated table, we can ensure that the resulting distinct set is part of the model structure and available for subsequent relationships and calculations.

The primary function utilized for aggregating and summarizing data to extract unique column combinations is the SUMMARIZE function. This function returns a summary table from the input table, grouped by the columns specified. When no aggregation expressions are provided alongside the grouping columns, the function effectively performs a distinct operation across those columns, generating a new table consisting solely of the unique combinations requested.

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. This new table is critical for establishing efficient one-to-many relationships in your data model.

Understanding the SUMMARIZE Function Architecture

The architecture of the SUMMARIZE function is designed for powerful summarization. It requires, at minimum, the source table and one or more grouping columns. When used purely for generating distinct sets, it is crucial to understand that it operates on the row context established by the grouping columns. For instance, if you specify three columns, the resulting table will contain all unique combinations of those three values that exist in the underlying data.

While DAX also offers the `DISTINCT` and `VALUES` functions, these are typically used to return unique values from a single column, or used within aggregation contexts. The `SUMMARIZE` function is the most direct and efficient method when the goal is to extract the unique combination across two or more columns simultaneously and output this result as a standalone calculated table within the data model.

It is important to note the difference between using `SUMMARIZE` and using Power Query‘s “Remove Duplicates.” The Power Query transformation modifies the data source itself before it enters the model, resulting in a cleaner, smaller data set loaded into memory. Conversely, the DAX `SUMMARIZE` function creates a new, separate table within the model based on the existing data, preserving the original table intact for other reporting needs.

Practical Scenario: Initial Data Structure

Suppose we have the following table in Power BI named my_data that contains extensive information about basketball players on various teams. This data, which often originates from transaction logs or multiple entry points, naturally contains significant redundancy across the key attributes we wish to analyze:

Notice the inherent redundancy: Team ‘A’ has multiple entries associated with the ‘Guard’ position, and Team ‘B’ similarly has duplicates for the ‘Forward’ position. If we intend to create a separate dimension table for filtering or cross-reference, this redundancy must be eliminated to ensure efficient data modeling and accurate distinct counting.

Suppose that we would like to create a new table that extracts the distinct values across the Team and Position columns. This new table will serve as a dimension table linking to the primary my_data fact table.

Step-by-Step Implementation of the DAX Solution

The process of implementing this DAX calculation to achieve the distinct multi-column combination is straightforward and performed within the Power BI Desktop environment, specifically in the Data or Model View. The goal is to define a new table that will exist alongside the original my_data table.

To do so, click the Table tools tab along the top ribbon, then click the New table icon:

Once the formula bar is active, the critical step is to input the specific DAX formula that utilizes SUMMARIZE, referencing the source table and the desired grouping columns. Then type the following formula into the formula bar:

Distinct Table = SUMMARIZE(my_data, [Team], [Position])

Executing this formula instructs the Power BI engine to evaluate my_data, aggregate rows based on the unique pairing of Team and Position, and project this resulting set into the newly created table named Distinct Table. This new table will automatically appear in your data model ready for use.

Reviewing the Output: The Deduped Table

Upon successful execution of the DAX formula, a new object, Distinct Table, is instantiated in the data model. Observing the content of this new table confirms that the redundancy has been successfully eliminated, leaving only the unique combination of the specified columns.

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, structured as follows:

A comparison with the original my_data table highlights the efficiency of this method. 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. This consolidation is fundamental for dimensional modeling.

Similarly, any combination that previously appeared in duplicate—such as Team “B” and Position “Forward”—is now consolidated into a singular entry. The resulting table is a clean, minimal representation of the unique relationships between the Team and Position attributes found within the comprehensive source data, optimizing it for filtering and relationship creation.

Alternative Data Preparation: Power Query Deduplication

While the DAX method is excellent for creating calculated tables within the model, it is often more efficient to handle deduplication during the data loading and transformation phase using Power Query. The Power Query Editor provides a highly visual and intuitive interface for preparing data before it enters the model. This is particularly advantageous as it reduces the size of the loaded data model, minimizing memory consumption and improving query refresh speeds.

To achieve distinct values across multiple columns in Power Query, you would typically follow a dedicated procedure. First, load the relevant data into the Power Query Editor. Next, select the exact columns—in our example, [Team] and [Position]—that define the desired unique combination. With these columns highlighted, right-click the header of one of the selected columns and choose “Remove Duplicates.” Power Query then processes the data, retaining only the first instance of each unique combination found across the selected columns.

Alternatively, the “Group By” feature in Power Query can also be utilized for identifying distinct sets, although its primary function is aggregation. By grouping the data based on both [Team] and [Position] and choosing “All Rows” as the aggregation operation, you effectively summarize the data down to the unique groupings. While slightly less direct than “Remove Duplicates” for this specific purpose, it offers greater flexibility if subsequent aggregation steps are required.

Strategic Considerations for Distinct Value Retrieval

The decision between using Power Query or DAX for extracting distinct values hinges on several practical factors related to performance and data management. If the requirement is to clean the source data permanently and reduce the size of the model, Power Query is the superior choice because it handles the transformation at the extraction stage, minimizing the final model footprint.

However, if the distinct list must respond dynamically to filtering or context changes in the report, or if you need to derive the distinct set based on calculations performed within the model, the DAX approach using SUMMARIZE is necessary. Calculated tables created with DAX, while residing in memory, are evaluated dynamically when the data model refreshes, making them responsive to the underlying data updates without requiring a full re-transformation of the source queries.

Further Resources: For in-depth technical documentation regarding the syntax and advanced applications of aggregation functions, refer to the official Microsoft documentation.

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

Related Power BI Tutorials

To further enhance your skills in data preparation and modeling, explore other common tasks and tutorials in Power BI, such as those detailing row context, filter context, and iterative functions. Understanding these foundational concepts is key to becoming proficient in advanced data analysis within the platform.

Mastering the retrieval of distinct, multi-column value combinations ensures the integrity of your dimensional structures and greatly improves the efficiency and performance of your entire data model.