Downloads
DAXFilter and contextIntermediate

DISTINCT

Returns the unique values of a column, or the table with duplicate rows removed.

Syntax
DISTINCT(<column>) or DISTINCT(<table>)

Returns: A table.

What it does

DISTINCT returns the unique values of a column, or a table with duplicate rows removed. It respects the current filters, the same way VALUES does. The one difference from VALUES is that DISTINCT does not add the blank row that VALUES can add for keys with no matching dimension row. Use it when you need a clean list or a count of real members.

Syntax and arguments

Example

The number of customers who bought in the current filter.

Customers Who Bought =
COUNTROWS ( DISTINCT ( Sales[CustomerKey] ) )

Each customer key counts once, however many sales lines the customer has. DISTINCTCOUNT(Sales[CustomerKey]) is the shorter form of the same count and is more common in models.

Example: a second use

A clean list of countries for a slicer or a calculated table.

Store Country List =
DISTINCT ( Stores[Country] )

As a calculated table, the list is stored when the model refreshes. Every country that appears in Stores appears once, which gives a tidy dropdown list for a report.

Things to watch

In business terms

Customer and vendor counts for retention or spend reviews, clean category or entity lists for dropdowns, and unique invoice or account lists when a report must not double count.

Related functions

All DAX functions · View as Markdown · DAX fundamentals