DISTINCTCOUNT
Counts the unique values in a column.
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
- column: a column reference such as
Sales[CustomerKey]. Only one column is accepted. To count distinct combinations of columns, use COUNTROWS with SUMMARIZE or VALUES.
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
- A blank is treated as one distinct value in some cases. If blank keys are possible, check whether the count includes one extra item, and compare with a filtered count.
- DISTINCTCOUNT is not additive. The grand total of a matrix counts unique customers across all rows, so it is not the sum of the rows. That is correct behavior, but readers often expect a sum.
- It counts the values of one column. A count of distinct combinations needs COUNTROWS with VALUES or SUMMARIZE.
- On very large tables it is slower than COUNTROWS. It is still the right tool for distinct counts.
- Text values are compared as they are. Two spellings of the same vendor name count as two vendors, so clean the master data first.
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.