DISTINCT

DISTINCT is a DAX function that returns a one-column table with unique values from a specified column or a table with unique combinations of multiple columns.

Syntax

DISTINCT( ColumnNameOrTableExpr )

Argument	Properties	Description
ColumnNameOrTableExpr		The column (or table expression) from which unique values (or combination of values) are to be returned.

Return Values

A column of unique values.

Remarks

• The results of DISTINCT are affected by the current filter context. For example, if you use the formula in the following example to create a measure, the results would change whenever the table was filtered to show only a particular region or a time period.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

The following formula counts the number of unique customers who have generated orders over the internet channel. The table that follows illustrates the possible results when the formula is added to a report.

= COUNTROWS(DISTINCT(InternetSales_USD[CustomerKey]))

You cannot paste the list of values that DISTINCT returns directly into a column. Instead, you pass the results of the DISTINCT function to another function that counts, filters, or aggregates values by using the list. To make the example as simple as possible, here the table of distinct values has been passed to the COUNTROWS function.

Row Labels	Accessories	Bikes	Clothing	Grand Total
2005		1013		1013
2006		2677		2677
2007	6792	4875	2867	9309
2008	9435	5451	4196	11377
Grand Total	15114	9132	6852	18484

Also, note that the results are not additive. That is to say, the total number of unique customers in 2007 is not the sum of unique customers of Accessories, Bikes and Clothing for that year. The reason is that a customer can be counted in multiple groups.

Related functions

Other functions related to DISTINCT are:

• VALUES

Contribute » | Contributors: Rick de Groot