Power Pivot Principles: The A to Z of DAX Functions – DISTINCTCOUNT

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DISTINCTCOUNT. 

The DISTINCTCOUNT function

The DISTINCTCOUNT function is one of the aggregation functions.  It helps to count the number of distinct values in a column.  It has the following syntax:

The DISTINCTCOUNT function has only one [1] argument:

• column: this is required, and it represents the column that contains the values to be counted.

Some comments about the DISTINCTCOUNT function:

• the only argument allowed in this function is a column.  The column argument can contain any type of data
• if the function finds no row to count it will return a BLANK.  Otherwise, it returns the count of the distinct values
• the DISTINCTCOUNT function counts the BLANK value, whereas the DISTINCTCOUNTNOBLANK function does not count BLANK values
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules
• you may replicate the DISTINCTCOUNT function with the DISTINCT function and the COUNTROWS function.  For instance, you may write the following DAX code to compare the DISTINCTCOUNT function with the nested DISTINCT and the COUNTROWS functions:
• DISTINCTCOUNT(Table[Column Name])
• COUNTROWS(DISTINCT(Table[Column Name])).

Let’s consider the following example where we have the following Customer table (data is displayed in full):

We may write the following DAX code to count the unique Customer Name records and group them by Customer Group:

This will result in the following table:

If we add one [1] more product key with a BLANK Customer Name in the Customer table, it will be counted as well (see last row): 

After refreshing the data, it should return the following table (assuming the addition is to “Wholesale”):

Thus, you can see that the Wholesale Customer Group received one [1] more count from the last row even though the Customer Name is BLANK.  Therefore, to avoid this issue you should consider the DISTINCTCOUNTNOBLANK function.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.