Why You Should Stop Using DISTINCTCOUNT for Unique Value Calculations in Power BI

Many Power BI developers use DISTINCTCOUNT() when they want to count unique values in a column. On the surface, it seems like the right choice, but there’s a catch.

Before we dive into the deeper explanation, let’s first clarify a few fundamentals. What does DISTINCTCOUNT() actually do, and how does it work? To understand it properly, we need to revisit some basic principles of data modeling.

The DISTINCTCOUNT() function returns the number of distinct values in a given column. That sounds simple, but we need to clarify what we mean by “distinct values” and how they differ from “unique values.” These terms are often used interchangeably, but they have different meanings in the context of data analysis.

What are Distinct values and what are Unique values?

Distinct values are all the different entries in a column, regardless of how many times each value appears.

Example:

CustomerID	Distinct Values	Unique Values
101	101	102
102	102	104
101	103
103	104
104
103




From this example we can confirm that:
Distinct values are all the different values that exist in a column, we count each value only once, no matter how many times it appears.

In our example:

• 101 appears twice ✅ (still counts once)

• 102 appears once ✅

• 103 appears twice ✅ (still counts once)

• 104 appears once ✅

✅ So the distinct values are: 101, 102, 103, 104

Total distinct values: 4

Unique values refer to values that appear only once in a column. These are the entries that are not repeated.

In our example:

• 101 appears 2 times ❌

• 102 appears 1 time ✅

• 103 appears 2 times ❌

• 104 appears 1 time ✅

✅ So the unique values are: 102, 104

Total unique values: 2

Now that we’ve confirmed what unique and distinct values are, it’s easy to say:

“If I want to calculate the number of distinct values in a column, I’ll just use the DISTINCTCOUNT() DAX function.”

And yes, in most cases, that’s the correct and straightforward approach. DISTINCTCOUNT() is designed to count all different (distinct) values in a column, and it works great in well-structured data models.

However, there are some situations where you won’t get the result you expect. This happens especially often when you’re working with data imported from Excel files which many analysts do.

Let me show you what I mean with a simple example below.

Example:

Suppose a column contains:

CustomerID:
101
102
101
103
null
102

DISTINCTCOUNT([CustomerID]) will return result 4. But how 4 when we have only 3 distinct values and that is 101,102 and 103. And here is the catch I mentioned in beggining.

DISTINCTCOUNT counts BLANK values and therefore we get 4 as result (101,102,103 and blank)

While technically accurate, in most analytical scenarios, blanks don’t represent meaningful data. Including them can skew results and lead to misleading insights.

---

SOLUTION:

A Better Alternative: DISTINCTCOUNTNOBLANK()

DISTINCTCOUNTNOBLANK() works just like DISTINCTCOUNT() but excludes blanks from the count.

Example (same data):

CustomerID:
101
102
101
103
null
102

DISTINCTCOUNTNOBLANK([CustomerID]) returns 3 – only the actual values.

This function gives a clearer picture of your data by ignoring missing or irrelevant entries.

---

Real-World Scenario: [CustomerID] Column

Your dataset:

• DISTINCTCOUNT([CustomerID]) = 4 → counts: 101, 102, 103, and Blank

• DISTINCTCOUNTNOBLANK([CustomerID]) = 3 → counts: 101, 102, 103

---

Performance

Both functions are optimized in Power BI, but on very large columns with many unique values, performance can dip. Keep your data model clean and avoid unnecessary complexity when nesting these functions.

---

Final Thought

The choice between DISTINCTCOUNT() and DISTINCTCOUNTNOBLANK() affects how you interpret your data. Ask yourself: Does a blank mean something, or is it just missing? That answer determines which function you should use for accurate, trustworthy results.

But just to be safe, I would go with DISTINCTCOUNTNOBLANK() instead, especially if your data source is an Excel or CSV file.

You can find more in video below: