Counting Unique Values Using DISTINCTCOUNT and DISTINCTCOUNTNOBLANK in DAX

September 28, 2026 132 visits

In this article, we will learn how to count unique values in DAX using
DISTINCTCOUNT and DISTINCTCOUNTNOBLANK. We will also understand
the important difference between these two functions when a column contains blank values.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist

What Is DISTINCTCOUNT in DAX?

DISTINCTCOUNT counts the number of distinct values in a column.
If the same value appears multiple times, it is counted only once.

This is useful when you want to answer questions such as:

  • How many unique customers are present?
  • How many unique products are available?
  • How many different categories exist?
  • How many unique values are present in a column?
DAX Syntax
DISTINCTCOUNT(<Column>)

The function accepts a column and returns the number of distinct values found in that column.

1. Reviewing the CountTable

Query

DAX Query
EVALUATE
	CountTable

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of a table expression in a DAX query.

Step 2: CountTable
CountTable is the table expression being returned. Running this query allows us to inspect
the values in Column A, Column B, and Column C before counting their unique values.

Key takeaway:
Before performing a distinct count, it is useful to inspect the source data and identify whether
duplicate or blank values are present.

2. Counting Unique Values Using DISTINCTCOUNT

Query

DAX Query
EVALUATE
	ROW(
		"Col A", DISTINCTCOUNT(CountTable[Column A]),
		"Col B", DISTINCTCOUNT(CountTable[Column B]),
		"Col C", DISTINCTCOUNT(CountTable[Column C])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.

Step 2: ROW
ROW creates a single-row table containing the results of the expressions provided to it.
Here, we use it to display the distinct count for all three columns in one result.

Step 3: “Col A”, DISTINCTCOUNT(CountTable[Column A])
This calculates the number of unique values in Column A and displays the result under the name
Col A.

Step 4: “Col B”, DISTINCTCOUNT(CountTable[Column B])
This calculates the number of unique values in Column B.

Step 5: “Col C”, DISTINCTCOUNT(CountTable[Column C])
This calculates the number of unique values in Column C.

What will you get?

The query returns a single row with one distinct-count result for each column.
The exact numbers depend on the values available in your CountTable.

Col A Col B Col C
Unique count of Column A Unique count of Column B Unique count of Column C

The result values above are descriptive placeholders. Your actual DAX query will return the distinct counts from your CountTable.

Key takeaway:
DISTINCTCOUNT counts unique values in a column and returns the count as a scalar value.

3. Counting Unique Values Without Counting Blanks

Sometimes a column contains blank values. If you want to count distinct values while excluding the blank value,
DAX provides DISTINCTCOUNTNOBLANK.

DAX Syntax
DISTINCTCOUNTNOBLANK(<Column>)

Query

DAX Query
EVALUATE
	ROW(
		"Col A", DISTINCTCOUNTNOBLANK(CountTable[Column A]),
		"Col B", DISTINCTCOUNTNOBLANK(CountTable[Column B]),
		"Col C", DISTINCTCOUNTNOBLANK(CountTable[Column C])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.

Step 2: ROW
ROW creates a single-row table containing the three distinct-count results.

Step 3: DISTINCTCOUNTNOBLANK for Column A
DISTINCTCOUNTNOBLANK(CountTable[Column A]) counts the distinct values in
Column A while excluding the blank value from the count.

Step 4: DISTINCTCOUNTNOBLANK for Column B
The same logic is applied to Column B.

Step 5: DISTINCTCOUNTNOBLANK for Column C
The same logic is applied to Column C.

Key takeaway:
Use DISTINCTCOUNTNOBLANK when the distinct count should not include a blank value.

4. DISTINCTCOUNT vs DISTINCTCOUNTNOBLANK

The main difference between these two functions is how they handle blank values.

Function Purpose Blank Value
DISTINCTCOUNT Counts distinct values in a column Can include blank as a distinct value
DISTINCTCOUNTNOBLANK Counts distinct values in a column Excludes blank from the count

Simple Example

Suppose a column contains the following values:

Value
Red
Blue
Red
Green
Blank

There are 3 non-blank unique values: Red, Blue, and Green.
The blank value is an additional distinct value when blank is included.

Function Result
DISTINCTCOUNT 4
DISTINCTCOUNTNOBLANK 3
Important:
The difference between the two functions becomes visible when the column contains a blank value.

5. Why Use ROW in These Queries?

In this example, ROW is used to place multiple scalar results into a single-row table.
This makes it convenient to compare the distinct counts of several columns in one query result.

Example
EVALUATE
	ROW(
		"Col A", DISTINCTCOUNT(CountTable[Column A]),
		"Col B", DISTINCTCOUNT(CountTable[Column B]),
		"Col C", DISTINCTCOUNT(CountTable[Column C])
	)

Each name-expression pair creates one column in the resulting row.
For example, “Col A” is the output column name and
DISTINCTCOUNT(CountTable[Column A]) is the expression used to calculate its value.

6. Understanding the Two Queries Together

The first counting query uses DISTINCTCOUNT, while the second uses
DISTINCTCOUNTNOBLANK.
Running both queries against the same table helps you understand the impact of blank values on distinct counts.

Query What It Does
DISTINCTCOUNT Counts distinct values and can include blank as a distinct value
DISTINCTCOUNTNOBLANK Counts distinct values while excluding blank

📝 Practice Exercise

Use the CountTable and write queries to answer the following:

  1. Count the distinct values in Column A.
  2. Count the distinct values in Column B.
  3. Count the distinct values in Column C.
  4. Repeat the analysis using DISTINCTCOUNTNOBLANK.
  5. Compare the results and identify which columns contain a blank value that affects the distinct count.

Challenge

Create a single ROW query that displays both the distinct count and the non-blank distinct count
for each column.

Challenge Query Structure
EVALUATE
	ROW(
		"Col A Distinct", DISTINCTCOUNT(CountTable[Column A]),
		"Col A No Blank", DISTINCTCOUNTNOBLANK(CountTable[Column A]),
		"Col B Distinct", DISTINCTCOUNT(CountTable[Column B]),
		"Col B No Blank", DISTINCTCOUNTNOBLANK(CountTable[Column B]),
		"Col C Distinct", DISTINCTCOUNT(CountTable[Column C]),
		"Col C No Blank", DISTINCTCOUNTNOBLANK(CountTable[Column C])
	)

Conclusion

In this article, we learned how to count unique values using DISTINCTCOUNT and
DISTINCTCOUNTNOBLANK in DAX.

  • DISTINCTCOUNT counts distinct values in a column.
  • DISTINCTCOUNTNOBLANK counts distinct values while excluding blank.
  • ROW can be used to display multiple scalar calculations in a single-row result.
  • Comparing both functions helps identify the impact of blank values on a distinct count.

These functions are especially useful when building analytical metrics such as unique customers,
unique products, unique categories, and other distinct-value measurements in Power BI and DAX.

Next: We can continue exploring DAX functions for filtering, table manipulation,
and advanced calculations.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist