What is the Power BI DISTINCT function?
The Power BI DISTINCT is a table manipulation function that can be used to return either a unique list of values from the input column or a DAX expression evaluating a column. Similarly, you can also use this function to return a table by removing duplicate rows from a table expression. Hence, in these contexts, the Power BI DISTINCT function is a powerful tool where you have a choice of usage to return unique values from a column or table.
It can be used to obtain a list of unique values for a filter, slicer, etc., or analysis of unique items.
Table of contents
Key Takeaways
- The Power BI DISTINCT function is a table manipulation function that returns a unique list of values from the input column or a DAX expression evaluating a column.
- You can return a table by removing duplicate rows from another table or table expression.
- Power BI DISTINCT can be extremely useful for identifying the unique values in your dataset and performing data analysis.
- You can use this function to create a calculated column, measure, or table in Power BI.
- You can use the Power BI DISTINCT function in conjunction with other Power BI functions, such as COUNTROWS, CALCULATE, SUMX, etc., to realize maximum benefits from it.
Syntax
The Power BI DISTINCT function can be used in the context of a column or table.
The syntax for Power BI DISTINCT is highlighted below:
DISTINCT(<ColumnNameOrTableExpr>)
It accepts a single parameter that includes either of the following:
| Parameter Name | Description |
|---|---|
| ColumnName | The column or an expression returning a column from which unique values will be returned |
| TableExpr | The table or a table expression from which unique rows will be returned |
How to use the Power BI DISTINCT function?
To use the Power BI DISTINCT function, you can follow the below steps:
Step 1: Launch Power BI Desktop in your system.
Step 2: In the Home tab, click on Get data to import your dataset and then load it into Power BI using the Load button.
Step 3: Navigate to the Modeling tab and click on the New table option.
Step 4: Write the DAX expression in Power BI in the formula bar using the DISTINCT function to create a table for your reporting requirements.
In this case, financials is the table that will be used for screening the unique values by the DISTINCT function.
DistinctTable = DISTINCT(financials)
Click on the Commit icon to save the changes. It will create a table of unique values from the financials table.
Step 5: Navigate to the Table view pane and choose the newly created table. You can view the datasets in the table, as shown below.
Step 6: Use the table to create visuals in Power BI or calculations for your reporting requirements.
Note: You can also use Power BI Distinct Count with condition by to create a new measure for your reporting purposes. For this purpose, you can use the FILTER function in Power BI in conjunction with DISTINCT for calculation purposes.
In the next section, we will demonstrate how you can use the Power BI Distinct function with the help of a few examples.
Examples
In this section, we will go through different scenarios demonstrating the use of the DISTINCT function in Power BI.
Example #1
In this example, we will demonstrate the Power BI Distinct multiple columns filter to create a measure. For this demo, we have used road_accident_dataset data. The road_accident_dataset dataset contains the details of different road accidents, including time, road conditions, nature of accidents and fatalities, etc.
To use the Power BI Distinct multiple columns filter, follow the instructions below:
Step 1: Open the Power BI Desktop in your system. In the Home tab, click on the Get data option to Import the dataset. Click on the Load button to load it into Power BI.
Step 2: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu. It will open a formula bar prompt.
Step 3: Write the DAX expression using the DISTINCT function to create a new measure.
In this example, we have created 3 measures using the DISTINCT function as shown below:
- Total count of road accidents based on IDs
- Total Unique Accidents in Rainy Weather conditions by filtering the weather conditions
- Total Unique Accidents in New York involving Teenagers and Young Adults by filtering State and Driver_Agr_Group
–Measure 1
Total count of road accidents = COUNTROWS(DISTINCT(road_accident_data[ID]))
–Measure 2
Total Unique Accidents in Rainy Weather conditions = COUNTROWS(DISTINCT(FILTER(road_accident_data, road_accident_data[Weather_Conditions] = “Rainy”)))
–Measure 3
Total Unique Accidents in New York involving Teenagers and Young Adults = COUNTROWS(DISTINCT(FILTER(road_accident_data, road_accident_data[State] = “New York” && road_accident_data[Driver_Age_Group] in {“Teenager”, “Young Adult”})))
Once the changes are done, click on the Commit icon to save the changes after each measure. This will create 3 different measures for the road_accident_data table.
Step 4: Navigate to the Visualizations pane and select the card visual icon. Drag and drop the newly created measure to this card visual. Repeat this step for mapping the other 2 measures. This will create 3 card visuals in the report canvas in the Power BI.
Example #2
Let’s now look at another example of Power BI Distinct Count along with Power BI distinct multiple columns filter using the Amazon book dataset. The Amazon book dataset contains the details of the book including Title, Author, Genre, Price, Rating, Number of People rated, and URL of the book.
To use the Power BI Distinct Count along with the Power BI Distinct multiple columns filter, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option available in the Home tab. Click on the Load button to load it into the Power BI data model.
Step 2: Navigate to the Modeling tab. Select the New measure option.
Step 3: Now, specify the logic to create the new measure using the DISTINCT function in the formula bar.
In this demo, we have created two measures. The 1st one is created using Power BI distinct sum. The other one is created by applying Power BI distinct multiple columns filters to the dataset. They include:
- Total Lay-off in the American continent in the Finance Industry by filtering Continent
- Unique count of companies in Asia continent laid off in 2023 by filtering on Continent and Year
–Measure 1
Total Unique count of books = COUNTROWS(DISTINCT(Books_df[Title]))
–Measure 2
Unique Count of Books priced above $10 = COUNTROWS(DISTINCT(FILTER(Books_df, Books_df[Price] > 10)))
–Measure 3
Books with Rating above 4 and people rated above 1000 = COUNTROWS(DISTINCT(FILTER(Books_df, Books_df[Rating] > 4 && Books_df[No. of People rated] > 1000)))
Hit on the Enter key to save the changes for these measures. You can view the newly created measures by navigating to the Data pane.
Step 4: Navigate to the Visualizations pane and select the card visual icon. Map the newly created measure to this card visual. Repeat the steps for each of the created measures to map to a card visual. Once the mapping is complete, you will see the card visuals in the report canvas as shown below
Example #3
In this example, we will demonstrate the Power BI distinct sum and Power BI distinct multiple columns filters using the Tech Layoffs 2020-2024 dataset. The Tech Layoffs 2020-2024 dataset contains the worldwide layoffs in tech companies across different industries along with company details, number of layoffs, coordinates, and total money raised in $ values, etc.
To demo the Power BI distinct sum and Power BI distinct multiple columns filters, follow the instructions below:
Step 1: Navigate to Home – Get data in Power BI Desktop. Select the dataset in the Navigator. Click on the Load button to load it into Power BI.
Step 2: Navigate to the Data pane. Select the table, right-click on it, and choose New measure from the context menu.
Step 3: In the formula bar, specify the DAX expression using the DISTINCT function to create a new measure.
In this demo, we have created 2 measures. The 1st one is created using Power BI distinct sum. The other one is created by applying Power BI distinct multiple columns filters to the dataset. They include:
- Total Lay-off in American continent in Finance Industry by filtering Continent
- Unique count of companies in Asia continent laid off in 2023 by filtering on Continent and Year
–Measure 1
Total Lay-off in American continent in Finance Industry = SUMX(DISTINCT(FILTER(‘tech_layoffs_2020-2024’, ‘tech_layoffs_2020-2024′[Industry] = “Finance” && ‘tech_layoffs_2020-2024′[Continent] = “North America”)),’tech_layoffs_2020-2024′[Laid_Off])
–Measure 2
Unique count of companies in Asia continent laid off in 2023 = COUNTROWS(DISTINCT(FILTER(‘tech_layoffs_2020-2024’, ‘tech_layoffs_2020-2024′[Continent] = “Asia” && ‘tech_layoffs_2020-2024′[Year] = 2023)))
Click on the Commit icon to save the changes for your measures.
Step 4: To create a visual using the newly created measures, navigate to the Visualizations pane and select the card visual icon. Drag and drop the newly created measure to this card visual.
Once the mapping is completed, you will see the data visualization in the report canvas as provided below.
Important Things to Note
- If you are using any filter in combination with the DISTINCT function, the results of DISTINCT may be affected by the current filter context.
- It is recommended that the proper relationship between the tables is established if you are using the RELATED function in the context of Power BI DISTINCT.
- Be aware that using this function on a large dataset may impact the overall performance
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules
Frequently Asked Questions (FAQs)
The VALUES function is similar to the DISTINCT function in Power BI. In terms of output, both functions return a unique list of values, remove duplicates, and produce identical results for the input column. However, the point where they differ is VALUES function can return a BLANK value.
• For the column parameter, this specific use case can be useful to analyze where you are performing a look-up on the unique values from a related table but a value referenced in the relationship is not available in one table. This scenario is also referred to as a violation of referential integrity in the database terminology.
• Similarly, for the table parameter, the VALUES function returns all the rows from the input table and a blank row in case there is any violation of referential integrity which is not the case for the DISTINCT function.
Yes, you can use the DISTINCT function in Power Query Editor (M language). You can use the Table.Distinct function to return a table with unique record values.
For example:
// Removes duplicates based on all column combinations
Table.Distinct(Source)
// Removes duplicates based on the Column1, Column2
Table.Distinct(Source, {“Column1”, “Column2”})
Currently Power BI does not have any UNIQUE function, unlike DISTINCT. Power BI DISTINCT refers to the total number of different values in a column irrespective of the number of times it appears in the dataset. Unique refers to the total number of values that appear once.
Yes, you can use DISTINCT in conjunction with other functions in Power BI.
For example: The below DAX expression uses DISTINCT in conjunction with the COUNTROWS function to count the distinct products in the different outlets of an eCommerce company.
DistinctProducts = COUNTROWS(DISTINCT(financials[Product]))
This returns the below output displayed in a Card visual in Power BI.
Recommended Articles
Guide to Power BI DISTINCT. Here we explain how to use distinct function to obtain unique value from input, with examples & points to remember. You can learn more from the following articles –
Leave a Reply