The DISTINCT function that receives the name of a column as an input argument returns a table with a single column containing the unique values of the original column, that is, after eliminating duplicate values.
DISTINCT(
column
)
- column: Name of the column whose unique values you want to return as a result, or expression that returns a column.
The DISTINCT function returns a table with a single column containing the unique values of the input column.
The name given to the only column present in the table returned as a result is the same as that of the input column.
This function is contextualized each time it is used. In other words, the returned result may be different if the calculation context changes.
In most cases, the result returned by this function is the same as that returned by the VALUES function, since both functions remove duplicate values and return the list of unique values. But VALUES can include in said column a Blank (equivalent to "Unkown member") in those scenarios in which what is known as referential integrity violation occurs (for example, when information is being extracted from a related table but the value used in the relationship does not exist in one of the tables).
Suppose we have a Geography table that contains a list of postal codes along with the city, region and country to which each belongs. Suppose the country is in the Country field.
We can count the number of countries that exist in the table using the following DAX expression:
NumCountries = COUNTROWS(DISTINCT(Geography[Country]))
Continuing with the same example, we can create a calculated table "Countries" containing exclusively the unique values included in the Country field with the following code:
Countries = DISTINCT(Geography[Country])
