DISTINCT function is used to retrieve unique values from a column or a table in Power BI. The DISTINCT function eliminates duplicate values and returns a new table or column with only the unique values.
Syntax
The syntax for the DISTINCT function in Power BI DAX is:
DISTINCT(table/column)Here, the table/column parameter is the name of the table or column from which you want to retrieve the unique values.
Usage
The DISTINCT function can be used in various scenarios to analyze and manipulate data. Let’s explore some common use cases:
1. Removing Duplicate Values
One of the most common use cases of the DISTINCT function is to remove duplicate values from a column. For example, if you have a sales dataset with multiple entries for the same product, you can use the DISTINCT function to retrieve a list of unique products.
Here’s an example:
ProductList = DISTINCT(Sales[Product])This will create a new table called ProductList with only the unique products from the Sales table.
2. Counting Distinct Values
The DISTINCT function can also be used to count the number of distinct values in a column. This can be useful when you want to analyze the uniqueness of certain attributes in your dataset.
For example, if you have a customer dataset with multiple entries for each customer, you can use the DISTINCT function to count the number of unique customers.
UniqueCustomers = COUNTROWS(DISTINCT(Customer[CustomerID]))This will return the count of unique customers in the Customer table.
3. Filtering Data
The DISTINCT function can also be used in combination with other functions to filter data based on unique values. For example, you can use the DISTINCT function to create a slicer that allows users to select only the unique values from a column.
Here’s an example:
FilteredSales = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Product] IN VALUES(ProductList[Product])))In this example, we are using the DISTINCT function to create a slicer called ProductList with unique products. The FILTER function is then used to filter the Sales table based on the selected products from the slicer.
Conclusion
The DISTINCT function in Power BI DAX is a powerful tool for retrieving unique values from a table or column. It can be used to remove duplicate values, count distinct values, and filter data based on unique values. By understanding how to use the DISTINCT function effectively, you can enhance your data analysis and visualization capabilities in Power BI.