The Power BI DISTINCTCOUNT function is one of the DAX aggregate functions used to count the total number of distinct rows or records in a given column. This article explains the POWER BI DAX DISTINCTCOUNT function and the syntax is shown below:
DISTINCTCOUNT(<Column>)
The Power BI DAX DISTINCTCOUNT function works on any type of data and counts the rows.
- It returns blank when there are no rows to count in a column.
- By default, the DISTINCTCOUNT function counts the rows with BLANK values. To skip the blank values and return the distinct count, use DISTINCTCOUNTNOBLANK.
Power BI DAX DISTINCTCOUNT function Example
We need a Measure to work with the DISTINCTCOUNT function and assign the result of a distinct total product count. So, within the Home tab, click the New Measure button and rename it as DistinctProdCount. To demonstrate the DAX DISTINCTCOUNT() function, let me use the SuperStore Orders table Product ID column to obtain the total distinct number of products.
DistinctProdCount = DISTINCTCOUNT(Orders[Product ID])
Please add the new DistinctProdCount Measure to the table report. For example, within the Furniture Category, there is a Chairs subcategory and it has a total of 634 product sales. However, there are only 87 distinct products. Please refer to the Aggregate and the function article for the remaining Power BI functions. For more Charts >> Click Here.
The other option is to add the Product ID to the table. Next, click the down arrow beside the Product ID and change the aggregation to Count (Distinct) as shown below.
