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

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 DISTINCTCOUNTNOBLANK. 

The DISTINCTCOUNTNOBLANK function

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

It has just one [1] argument:

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

Some notes about the DISTINCTCOUNTNOBLANK function:

• the only argument allowed in this function is a column.  The column argument may contain any type of data
• if the function finds no row to count it will return a BLANK.  Otherwise, it returns the count of distinct values
• the DISTINCTCOUNT function includes BLANK values in its count, whereas the DISTINCTCOUNTNOBLANK function does not count BLANK values
• the DISTINCTCOUNTNOBLANK function is not compatible with Power Pivot and currently it is only compatible with Power BI, SSAS Tabular, Azure AS and SSDT
• 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 DISTINCTCOUNTNOBLANK function with the DISTINCTCOUNT function in combination with other functions:

As an example, consider the following Customer table that has been imported into Power BI:

We may create a measure to test out the DISTINCTCOUNTNOBLANK function:

We can create a measure using DISTINCTCOUNT to compare with the DISTINCTCOUNTNOBLANK measure:

We can display the results within a matrix visualisation as follows:

Thus, we will have the following output:

As you can see from the Customer data table above, there are two [2] BLANK values in the Customer Name column.  Therefore, the DISTINCTCOUNT function has a higher count in the Supermarket and Wholesale than the corresponding DISTINCTCOUNTNOBLANK function since the DISTINCTCOUNT function will also count the BLANK values in the dataset while the DISTINCTCOUNTNOBLANK will not.

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.