DISTINCTCOUNTNOBLANK DAX Function (Aggregation)
Counts the number of distinct values in a column.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| ColumnName |
The column for which the distinct values are counted, no blank value included. |
Return values
The number of distinct values in ColumnName, ignoring the blank value.
Remarks
DISTINCTCOUNTNOBLANK is syntax sugar for evaluating DISTINCTCOUNT removing BLANK from the filter context.
The syntax:
DISTINCTCOUNTNOBLANK ( table[column] )
corresponds to:
CALCULATE (
DISTINCTCOUNT ( table[column] ),
KEEPFILTERS ( NOT ISBLANK ( table[column] ) )
)
» 2 related functions
Examples
--
-- DISTINCTCOUNT considers BLANK as a valid value, whereas
-- DISTINCTCOUNTNOBLANK does not count any blank value
--
DEFINE
MEASURE Customer[# Stores] =
COUNTROWS ( Store )
MEASURE Customer[# Manager] =
DISTINCTCOUNT ( Store[Area Manager] )
MEASURE Customer[# Manager (no blank)] =
DISTINCTCOUNTNOBLANK ( Store[Area Manager] )
MEASURE Customer[# Stores without manager] =
COUNTBLANK ( Store[Area Manager] )
EVALUATE
SUMMARIZECOLUMNS (
Store[Continent],
"# Stores", [# Stores],
"# Manager", [# Manager],
"# Manager (no blank)", [# Manager (no blank)],
"# Stores without manager", [# Stores without manager]
)
ORDER BY
Store[Continent]
| Continent | # Stores | # Manager | # Manager (no blank) | # Stores without manager |
|---|---|---|---|---|
| Asia | 41 | 5 | 5 | (Blank) |
| Europe | 54 | 8 | 7 | 7 |
| North America | 209 | 40 | 39 | 5 |
More examples available for the DISTINCTCOUNT function.
Related articles
Learn more about DISTINCTCOUNTNOBLANK in the following articles:
-
Controlling drillthrough in Excel PivotTables connected to Power BI or Analysis Services
This article describes how to customize the drillthrough experience in Excel PivotTables connected to Power BI datasets or Analysis Services databases. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Mads Hannibal, Kenneth Barber,
Microsoft documentation: https://docs.microsoft.com/en-us/dax/distinctcountnoblank-function-dax