Difference between Distinct() and Values() in DAX

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.