DISTINCTCOUNT()
Learn how DISTINCTCOUNT counts unique values in a column, how it treats blanks, and when to use DISTINCTCOUNTNOBLANK instead.
DISTINCTCOUNT()
DISTINCTCOUNT() counts the number of distinct values in a column, evaluated within the current filter context.
DISTINCTCOUNT(Column)Basic Example
Unique Customers =
DISTINCTCOUNT(FactSales[CustomerKey])FactSales[CustomerKey]
101
102
101
103
102
DISTINCTCOUNT -> 3 (101, 102, 103 — each counted once)This is the standard way to answer "how many unique X" — unique customers, unique products sold, unique visits — from a fact table where the same key can legitimately repeat across many rows.
How DISTINCTCOUNT Handles Blanks
DISTINCTCOUNT() counts a blank value as one distinct value, if the column contains any blanks at all.
FactSales[PromoCode]
"SAVE10"
"SAVE10"
(blank)
"WELCOME"
DISTINCTCOUNT -> 3 ("SAVE10", blank, "WELCOME")If the intent is "how many distinct promo codes were actually used," including blank as a countable value overstates it by one. DISTINCTCOUNTNOBLANK() excludes the blank:
Promo Codes Used =
DISTINCTCOUNTNOBLANK(FactSales[PromoCode])DISTINCTCOUNTNOBLANK -> 2 ("SAVE10", "WELCOME")Try it live — clear or fill in a cell to add or remove a blank
FactSales[PromoCode]
counts blank as one distinct value
3
excludes blank entirely
2
— at least one row has no promo code at all. DISTINCTCOUNT() counts that blank as its own distinct value, one higher than the real number of promo codes actually used. If the question is "how many distinct promo codes were used," DISTINCTCOUNTNOBLANK()'s 2 is the honest answer — DISTINCTCOUNT()'s 3 overstates it by counting "no code" as if it were one.
DISTINCTCOUNT Is Filter-Context Aware
Like any DAX aggregation, DISTINCTCOUNT() only counts distinct values among the rows visible in the current filter context — not the whole table unconditionally.
Unique Customers =
DISTINCTCOUNT(FactSales[CustomerKey])No filters applied -> counts distinct customers across all sales
Category = "Bikes" filter -> counts distinct customers who bought Bikes specificallyTo get an unfiltered total for comparison (a "percent of all customers" style measure), wrap it in CALCULATE() with ALL():
Unique Customers (All Categories) =
CALCULATE(
DISTINCTCOUNT(FactSales[CustomerKey]),
ALL(DimProduct)
)See the DAX CALCULATE Modifiers Cheat Sheet for this pattern applied more generally.
Common Mistakes
Not Accounting for Blank Inflating the Count by One
A DISTINCTCOUNT() that's consistently one higher than expected is often the blank-counts-as-a-value behavior, not a data quality bug — check whether DISTINCTCOUNTNOBLANK() is actually the intended function.
Using DISTINCTCOUNT for a Multi-Column Distinct Count
DISTINCTCOUNT() only accepts a single column. Counting distinct combinations of two or more columns needs COUNTROWS() over a SUMMARIZE()'d table instead — see COUNTROWS.
Running It Against a High-Cardinality Column on a Huge Table
DISTINCTCOUNT() on a column with millions of unique values (a raw transaction ID, for example) is one of the more expensive DAX aggregations — worth checking whether a lower-cardinality key would answer the same business question.
Best Practices
- Use
DISTINCTCOUNTNOBLANK()when a blank value shouldn't count as a real, distinct answer. - Reach for
COUNTROWS()+SUMMARIZE()for a distinct count across more than one column. - Be mindful of running
DISTINCTCOUNT()against very high-cardinality columns on large fact tables — it's not free.