Daily DAX : Day 237 DISTINCTCOUNTNOBLANK

 The DISTINCTCOUNTNOBLANK function in Power BI DAX (Data Analysis Expressions) counts the number of unique, non-blank values in a column. It ignores blank or empty values and only considers distinct (unique) non-blank values.

Syntax

DISTINCTCOUNTNOBLANK(<column>)

    <column>: The column containing the values you want to count.

Return Value

    Returns an integer representing the count of unique, non-blank values in the specified column.

Key Characteristics

    Ignores blanks: Unlike DISTINCTCOUNT, which counts all unique values including blanks, DISTINCTCOUNTNOBLANK excludes blank or null values.

    Case-sensitive: It treats values like "Apple" and "apple" as distinct.

    Works on any data type: Can be used with text, numbers, dates, etc.

Use Case

DISTINCTCOUNTNOBLANK is useful when you need to count unique non-empty entries in a dataset, such as:

    Customer Analysis: Counting unique customers who made purchases (excluding blank customer IDs).

    Inventory Management: Counting unique products sold, ignoring blank product codes.

    Survey Analysis: Counting unique non-blank responses to a survey question.

    Data Quality Checks: Identifying the number of unique valid entries in a column to ensure data integrity.

Example

Suppose you have a table Sales with a column CustomerID:

CustomerID                                SalesAmount

C001                                                100

C002                                                 150

C001                                                 200

                                                          50

C003                                                 300

                                                          75

DAX Formula:

UniqueCustomers = DISTINCTCOUNTNOBLANK(Sales[CustomerID])

Result: 3

    Explanation: The unique non-blank CustomerID values are C001, C002, and C003. Blank values are ignored, and duplicates (C001 appears twice) are counted only once.

Comparison with Similar Functions

    DISTINCTCOUNT: Counts all unique values, including blanks.

        Example: DISTINCTCOUNT(Sales[CustomerID]) would return 4 (including the blank as a unique value).

    COUNTBLANK: Counts only blank values.

    COUNTA: Counts all non-blank values, including duplicates.

Practical Implementation

You can use DISTINCTCOUNTNOBLANK in:

    Measures: To calculate metrics like unique customers or products for reports.

    Calculated Columns: To derive insights based on unique counts.

    Filters: To analyze subsets of data (e.g., unique customers per region).

Example in a Measure

UniqueProductsSold = DISTINCTCOUNTNOBLANK(Sales[ProductID])

This measure could be used in a Power BI visual to show the number of unique products sold in a given time period or region, ignoring any blank product IDs.

Notes

    Ensure the column used has meaningful data, as blank values are common in poorly cleaned datasets.

    Performance may be impacted with very large datasets, so optimize by filtering data where possible.

    If you need to include blanks in the count, use DISTINCTCOUNT instead.

This function is particularly valuable in business intelligence for summarizing and analyzing data while excluding irrelevant blank entries.