Difference between Distinct() and Values() in DAX

Question

What is the difference between Distinct() and Values() in DAX

Jaish Mathews · Answer

In DAX (Data Analysis Expressions), DISTINCT() and VALUES() are two functions that seem similar at first glance but have key differences in behavior and use cases. 1. DISTINCT() Purpose : Returns a one-column table that contains the unique values from a specified column or expression. Behavior : Removes duplicates from the specified column or table expression. Always returns a table with the distinct values, regardless of the current filter context. Typical Use Cases : To calculate unique values in a dataset. Useful in aggregation and when filtering operations need to consider distinct values. Example : DISTINCT(Sales[Product]) This returns a table of all unique products in the Sales table. 2. VALUES() Purpose : Returns a one-column table of unique values for a column, but it respects the current filter context. Behavior : If there is a filter applied to the column, VALUES() returns only the unique values visible under that filter. If no filter is applied, it behaves similarly to DISTINCT() . If all rows in the column are blank, it returns an empty table. If the entire column is filtered out, it returns a blank value (different from DISTINCT ). Typical Use Cases : To retrieve values affected by the filter context. Often used in relationship calculations, measures, or scenarios where filter context sensitivity is required. Example : VALUES(Sales[Region]) This returns the unique regions in the Sales table, but only those visible in the current filter context. Key Differences Feature DISTINCT() VALUES() Filter Context Ignores the current filter context. Respects the current filter context. Empty Column Behavior Returns an empty table. Returns a single blank value. Primary Use For unique values in a global scope. For unique values in the current filter context. When to Use Which? Use DISTINCT() when you want the global unique values, regardless of any filters in place. Use VALUES() when you need to work within the current filter context and account for filters applied to the data.

Rajeesh Menoth · Answer

Hi Kiran, As per the microsoft docs : The DISTINCT function allows a column name or any valid table expression to be its argument but the VALUES function only accepts a column name or a table name as the argument. Reference : https://learn.microsoft.com/en-us/dax/values-function-dax

Difference between Distinct() and Values() in DAX

Answers (2)