ALL, ALLEXCEPT and VALUES in DAX

When you use CALCULATE in DAX you are creating a new filter context for the calculation, based on the existing one. There are a few functions that are used to clear or preserve a column filter. These functions are:

• ALL – it can be used with one or more columns from a table, or with the name of a table. It returns all the values from the column(s) or all the rows from the table, ignoring any existing filter context. In other words, ALL clear an existing filter context on columns or table.
  • We can use the ALL function with multiple columns, which could be useful whenever we want to list many columns from the same table. For example, instead of writing
    ALL( Orders[Channel] ), ALL( Orders[Color] ), ALL( Orders[Size] ), ALL( Orders[Quantity] ), ALL( Orders[Price] ), ALL( Orders[Amount] )
    we can write
    ALL( Orders[Channel], Orders[Color], Orders[Size], Orders[Quantity],  Orders[Price],  Orders[Amount] )
• ALLEXCEPT – if we want to remove filters from any column but only a few from a table, we can use ALLEXCEPT. In other words, using the Orders table as an example, the following statements are equivalent:
  CALCULATE( SUM( Orders[Amount] ),
  ALL( Orders[Channel], Orders[Color], Orders[Size],
  Orders[Quantity], Orders[Price], Orders[Amount] ) )
  CALCULATE( SUM( Orders[Amount] ),
  ALLEXCEPT( Orders, Orders[City] ) )
  • In reality, there is a subtle difference: the expression based on ALLEXCEPT function will operate also when there are relationships from other tables, ignoring possible filters implicitly included by relationship. To get the same result as for ALLEXCEPT by using the list of ALL calls, we should include an ALL call also for each table related to the one we are calculating on (Orders in this example).
  • In other words, CALCULATE(…, ALL(T) or ALLEXCEPT(T, …)) will remove filters not only from T but also from its related tables, while ALL(T[C]) doesn’t have an impact on tables related to T. As a result, the value of CALCULATE(…, ALLEXCEPT(…, T[C])) is affected only by the slice on T[C] as slices on other columns have been masked by columns returned from ALLEXCEPT.
• VALUES – this function returns a single column table with the distinct values of the given column cross filtered by all other slices in the filter context. Therefore, the value of CALCULATE(…, ALL(…), VALUES(T[C])) is affected by all slices that impact T[C]

When you operate with a PowerPivot model with a single table, these differences are not relevant. But when a model has many tables with relationships, then results are different. If we reference a model with three tables, Orders, Cities and Channels, we can say that these two expressions are equivalent:

CALCULATE( SUM( Orders[Amount] ),
ALL( Orders[Channel], Orders[Color], Orders[Size],
Orders[Quantity], Orders[Price], Orders[Amount] ) )

CALCULATE( SUM( Orders[Amount] ), ALL( Orders ), VALUES( Orders[City] ) )

But their result are different from these other two equivalent expressions:

CALCULATE( SUM( Orders[Amount] ),
ALL( Channels ), ALL( Cities ),
ALL( Orders[Channel], Orders[Color], Orders[Size],
Orders[Quantity], Orders[Price], Orders[Amount] ) )

CALCULATE( SUM( Orders[Amount] ), ALLEXCEPT( Orders, Orders[City] ) )

As I said, difference in results are evident when you choose filters for a pivot table on related tables, like Channels and Cities in our example. The two groups of expression we have seen consider or ignore the filters made on related tables, respectively.

A big thank to Jeffrey Wang (Microsoft) who has helped me to well understand this behavior and inspired me to writing this post.