DAX - ALL, VALUES and DISTINCT

DAX - ALL, VALUES and DISTINCT - Starting point

As discussed in a previous article - DAX ALL VS ALLEXCEPT - the ALL function—when applied to a single column—returns all the distinct values of that column. There are two other functions with similar behavior: VALUES and DISTINCT, although they differ in some important ways.

In a row context (such as in a calculated table), since there are no filters applied, we can use ALL, VALUES or DISTINCT and they will all return the same result, as shown in the screenshot. This is because each function simply returns all the distinct values of the column passed as its argument.

All(Column) = ALL(Categories[Category Name])

Values(Column) = VALUES(Categories[Category Name])

Distinct(Column) = DISTINCT(Categories[Category Name])

However, when use them in a filter context (like in a measure), their behavior diverges:

• ALL removes any filters applied to the column passed as its argument.

• while VALUES and DISTINCT, on the other hand, respect the active filters, and their results will change accordingly.

The main difference between VALUES and DISTINCT lies in how they handle BLANK() values. While VALUES includes BLANK() as a valid result, DISTINCT may not include it in certain scenarios.

But what exactly is a BLANK() row? A BLANK() row typically appears when there's a broken relationship between two tables in the data model—more specifically, when there are records on the "many" side of a relationship that do not have a corresponding match on the "one" side. In such cases, VALUES will return an additional row representing the blank, summing up all values from the unmatched records; this provides visibility into data that would otherwise be silently excluded. It’s also important to note that a BLANK() row will only appear once a relationship is established between two tables. For example, if we add a visual showing Category from the Category table and we count the rows using VALUES (see code below), nothing unusual will happen—until we create a relationship between the two tables. At that point, if there are unmatched values in the "many" side of the relationship, a BLANK() row will appear to represent those records.

Values Col = COUNTROWS(VALUES('Dim Category'[Category ID]))

NOTE: Since ALL, VALUES, and DISTINCT are table functions, they cannot return a scalar value on their own and therefore cannot be used directly as the output of a measure.

To use them in a measure, they must first be wrapped inside an aggregator or iterator—such as COUNTROWS, SUMX, or MAXX—which transforms the resulting table into a scalar value.

The visuals below show the difference that occurs before and after establishing the relationship between the two tables.

That said, what about the behavior of the ALL, and DISTINCT functions? Let’s break it down with a direct comparison between the three (see figure below):

• ALL ignores all filters—both explicit (like slicers or page filters) and implicit (like the row context from visuals). As a result, it always returns 9 in each row, because it includes all categories, including the one with a BLANK(), which is counted as a valid value.

• VALUES respects the implicit filter context—in this case, the Category column. Therefore, it returns 1 for each category row in the visual. Since VALUES considers BLANK() a valid value, it includes it in the total count, bringing the grand total to 9.

• DISTINCT also respects the implicit filter context, so it returns 1 for each category in the visual as well. However, unlike VALUES, DISTINCT does not consider BLANK() as a valid value in this context, so the grand total remains 8.

As you might have noticed, in this article we've used measure names like FunctionName Col — where "Col" stands for "Column"—to emphasize that the function was being applied to a single column. This naming choice was intentional, as it helps differentiate column-based usage from table-based usage.

The functions discussed in this article—ALL, VALUES, and DISTINCT—can also accept a table as their argument, and when they do, some of their behaviors change accordingly. Here’s a summary of how they behave when a table is passed as an argument:

• ALL(Table): Ignores all filters, returns duplicates (if present in the original table), and includes a blank row if there are unmatched rows due to a missing relationship.

• VALUES(Table): Respects filters, returns duplicates, and also includes the blank row for unmatched relationships.

• DISTINCT(Table): Respects all filters, returns only distinct rows, and does not include the blank row in case of unmatched relationships. dax - all, value and distinct

  
  

Conclusion To wrap up this article, here’s a quick summary of what we’ve covered today. I hope you found it useful and insightful:

---

Thanks for being a part of our community!

If you found this article helpful and would like to show your support, don’t hesitate to:

1. Clap on this story

2. Leave a comment below telling me what you think. This will help me with the next articles

3. Support my work on Buy Me a Coffee ☕️

These actions really really help me out, and are much appreciated!

Follow me for more insights on LinkedIn | YouTube | Medium