DISTINCT
Returns the unique values of a column, or the table with duplicate rows removed.
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
- column: returns each distinct value once.
- table: returns the rows with duplicates removed. Two rows are duplicates only when every column matches.
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 a measure,
DISTINCTreturns a table. Wrap it inCOUNTROWSto get a number. DISTINCTandVALUESdiffer only in the blank row. UseVALUESwhen you need to see that row, andDISTINCTwhen you do not.DISTINCTon a table with several columns compares all of them, so one changed column makes a new row.- A blank in the column is a real value for
DISTINCT. Decide whether it should count before you report the number.
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.