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] ) ) )
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 functions
Other related functions are:
Last update: Jun 6, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Mads Hannibal, Kenneth Barber,
Microsoft documentation: https://docs.microsoft.com/en-us/dax/distinctcountnoblank-function-dax