DISTINCTCOUNTNOBLANK DAX Function (Aggregation)
Counts the number of distinct values in a column.
Syntax
DISTINCTCOUNTNOBLANK ( <ColumnName> )
| Parameter | Attributes | Description |
|---|---|---|
| ColumnName |
The column for which the distinct values are counted, no blank value included. |
Return values
Scalar A single integer value.
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: Dec 8, 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
2018-2023 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us » Privacy Policy & Cookies