Downloads
DAXAggregationBeginner

DISTINCTCOUNT

Counts the unique values in a column.

Syntax
DISTINCTCOUNT(<column>)

Returns: A whole number (a scalar).

What it does

DISTINCTCOUNT counts how many different values a column holds, in the current filter context. Ten invoices from one customer count as one customer. It is the standard measure for "how many customers bought," "how many vendors were paid," or "how many accounts had activity." Use it when the question is about unique things, not about rows.

Syntax and arguments

Example

The sales VP wants the number of unique customers who bought in the year, to track customer reach separate from sales volume.

Unique Customers = DISTINCTCOUNT(Sales[CustomerKey])

Under a year slicer, the measure counts each customer key that has a sale that year. A customer with 40 purchases counts once. Compared with the number of sales lines, it shows how many purchases each customer makes on average.

Example: a second, different use

The number of distinct products sold to each customer segment, placed in a matrix with Customer[Occupation] on the rows:

Products Bought = DISTINCTCOUNT(Sales[ProductKey])

Each row of the matrix shows the number of different products bought by customers in that group. The same product bought by many customers still counts once in each group.

Things to watch

In business terms

DISTINCTCOUNT is how finance measures active customers, active vendors in accounts payable, employees paid in a period and accounts with a balance. Auditors use it to test vendor concentration: a small number of distinct vendors in a large spend pool is a risk signal.

Related functions

All DAX functions · View as Markdown · DAX fundamentals