The DISTINCTCOUNTNOBLANK DAX function is a variation of DISTINCTCOUNT in Power BI. It is used to count the number of distinct (unique) values in a column while excluding blank (null) values.
This function comes under the Aggregation functions category in DAX
Note:
- Counts the number of distinct values in a column, excluding blank values.
- Blank values are treated differently depending on the data type:
- Numeric or logical columns: Blank values are treated as null values.
- Text columns: Blank values are treated as empty strings.
- DirectQuery Limitation: Not supported in DirectQuery mode for calculated columns or row-level security (RLS) rules.
Syntax:
- column: The column containing the values to be counted.
Dataset – Orders
| CustomerID | OrderDate | Amount | CustomerCity |
| 1 | 15-11-2023 | 100 | Mumbai |
| 2 | 16-11-2023 | 50 | Delhi |
| 1 | 17-11-2023 | 80 | Bangalore |
| 3 | 18-11-2023 | 120 | Chennai |
| 19-11-2023 | 70 | Delhi | |
| 2 | 20-11-2023 | 90 | Delhi |
| 4 | 21-11-2023 | 150 | Kolkata |
| 22-11-2023 | 60 | ||
| 5 | 23-11-2023 | 110 | Hyderabad |
| 5 | 24-11-2023 | 40 | |
| 6 | 25-11-2023 | 80 | Pune |
| 26-11-2023 | 100 |
Now create two measures for DISTINCTCOUNTNOBLANK and DISTINCTCOUNT:
Right-click on the dataset name and select New Measure.
DistinctCountNoBlank = DISTINCTCOUNTNOBLANK(Orders[CustomerID])
DistinctCount = DISTINCTCOUNT(Orders[CustomerID])
Key Differences from DISTINCTCOUNT:
- DISTINCTCOUNT: Includes blank values in the count, if present.
- DISTINCTCOUNTNOBLANK: Counts the distinct values in a column, automatically excluding blank values.
DISTINCTCOUNTNOBLANK and DISTINCTCOUNT with Text Datatype
Now, we will count the values for a Text data type column. In our dataset, the CustomerCity column has a Text data type
For the Text data type, blank values are treated as empty strings, so they will be counted.
DistinctCountNoBlank_Text = DISTINCTCOUNTNOBLANK(Orders[CustomerCity])
DistinctCount_Text = DISTINCTCOUNT(Orders[CustomerCity])
Hope you enjoyed the post.
