The VALUES and DISTINCT functions in DAX look very similar because both return a unique list of values from a column or table — but there are a few key differences in how they behave, especially when it comes to blank values and filter context.
Both functions remove duplicates. For example:DISTINCT(Sales[CustomerID]) and VALUES(Sales[CustomerID]) will both return a table of unique Customer IDs.
However, the main difference is:
- DISTINCT always returns a list of unique values and ignores context beyond the given column.
- VALUES, on the other hand, returns unique values based on the current filter context, and it can also return a blank row if there are missing relationships or blank values in the column.
For example:
If your Sales[CustomerID] column has a few blank entries,
DISTINCT(Sales[CustomerID])will return only the actual customer IDs (excluding blank),- but
VALUES(Sales[CustomerID])will include the blank as a valid entry.
Another difference is that VALUES can also return an entire table (not just one column) when used without specifying a particular column — for example:VALUES(Sales) returns a table of unique rows (based on all columns), which DISTINCT cannot do directly.
In one of my Power BI dashboards, I used VALUES inside the CALCULATE function to dynamically capture the current filter selection — for instance, to show the selected customer name in a card visual using:SELECTEDVALUE(Customer[Name]),
which internally uses the VALUES function.
A challenge I faced was when using VALUES in measures for totals — sometimes, it included a blank value that caused total rows to show unexpected results. In those cases, switching to DISTINCT or wrapping it with REMOVEFILTERS() gave the expected output.
In short:
- DISTINCT → returns unique non-blank values.
- VALUES → returns unique values including blanks and respects the current filter context; it can also return a whole table.
So, when you’re writing a measure where blanks or filter context matter (like SELECTEDVALUE or dynamic titles), use VALUES; when you just need a clean unique list for aggregation or counting, use DISTINCT.