THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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.

Published Monday, April 05, 2010 5:05 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Carsten B. said:

This probably is not new, but I thing the first and the second calculate are not allways equivalent.

In case you have a city that has, e.g. just sales in one channel, and you have a slicer selection on orders[channel] that excludes this channel, than values(orders[city] will not return this city and therefore it will not be included in the calculation.

Even though all(orders) returns all rows and all cities, values(city) is still influenced by the original filter context, or query context. The solution would be to use filters(city), as it disregards any indirect filter.

October 26, 2012 5:56 PM
 

Marco Russo (SQLBI) said:

Carsten, you're correct, thanks for the clarification!

Marco

October 28, 2012 1:54 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement