THE SQL Server Blog Spot on the Web

Welcome to - 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


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 5, 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



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!


October 28, 2012 1:54 AM

Robin Sarac said:

Marco, thank you for this blog entry.  Very helpful.  ALLEXCEPT lets us tell DAX which column we want to KEEP in the current filter context.  Is there a function that lets us specify a column to REMOVE from the current filter context, keeping all others?

March 27, 2015 12:04 PM

Marco Russo (SQLBI) said:

Robin, you just have to write ALL ( Table[Column] )

March 27, 2015 12:09 PM

Linda said:

How can I have a PivotTable displaying all Order values filtered in context by City and another column showing Order values by City selected in the Slicer?

I ask because I want a Column PivotChart to show Order Values by City with a separate series highlighting (in a different colour) just those cities selected in the Slicer.

October 6, 2015 7:02 PM

Marco Russo (SQLBI) said:

Linda, you should use ALLSELECTED in that case. See this article for more details:

October 17, 2015 9:38 AM

Sazan said:


Can you please translate below sql to dax as calculated field in my cube:

SELECT   g.[Risk level]

 FROM [Datawarehouse].[Stg].[CM_Transaction] t, [MDS].[mdm].[vwGenesisRiskLevelRules_Set1] g

 where ='CreditLimitUtilisation'

 and [CreditLimitUtilisation] >= g.ValueFrom

 and [CreditLimitUtilisation] < = g.valueto

December 8, 2015 5:27 AM

Marco Russo (SQLBI) said:

Sazan, take a look at these two patterns:

You probably need the second one for a measure (aka calculated field in Excel 2013).

January 6, 2016 4:42 PM

Murat said:

how can i impose a filter in calculate on one of the related tables?

i have two related tables T1 and T2. I want to calculate the sum of a column in T1 at the combined level of T1[col_X] and T2[col_Y]. If it try the following:



ALLEXCEPT(T1, T1[col_X], T2[col_Y] ))

I see that it is summing up at the level of T1[col_X] but is not taking T2[col_Y] into account...


July 1, 2016 1:05 PM

Marco Russo (SQLBI) said:

What is the relationship between T1 and T2? Which table is on the one side and which one on the many side of the relationship?

August 17, 2016 10:43 AM

Vuong said:

Hi Marco,

I am having an issue like in the link. could you please help to suggest any solution>

I would be very thankful

April 6, 2017 2:17 PM

Adrian B said:

Hello Marco,

I have recently discovered something that I would call a strange behavior in Power BI Desktop(have not tested in SSAS, but I suspect is the same): in a report, when crossfiltering (or, to be precise, crosshighlighting) by clicking an item on a visual, besides navigating the relationships and setting the necessary filters in the related visuals, it also "injects" the filters set on that particular visual (Visual Level Filters) to the related visuals. Is this a bug ? If not, is there anyway in DAX to detect and remove the Visual Level Filters came as crossfilters from the context of a DAX formula ?

I can give you a more thorugh example if you feel like it.


August 22, 2017 7:26 AM

Marco Russo (SQLBI) said:

It is a Power BI behavior, you can modify how this using the "Edit interactions" command in the Format ribbon.

August 29, 2017 9:19 AM

Adrian B said:

I only wish it were that simple...  The thing is that cross-filtering based on the interacting with visuals is a desired behavior but only based on what's in the visual (what's set in the "Axis"/"Group"/"Location" in the right-hand "fields" pane), not based on what's set the "visual level filters".

If you need it, here's an example (please bear with me):

Let's say we have data model with a facts table and several dimensions. One of the dimensions is the KPI Name (Net Sales, Gross Sales, Tons, Bottles etc.).

In the facts table, besides links to the other dimensions there’s a link to the KPI and also the value of the KPI for that particular record. There’s a measure defined called KPI Value = CALCULATE (SUM(Facts[Value])) (it’s a little more complicated than this, but ultimately this is what it does).

Obviously when I use this measure in a visual I have to set a visual level filter for KPI Name (e.g. KPI Name = “Net Sales”) to display the correct value.

On a report I have 3 tiles displaying some KPI Values (thus having visual level filters each set for KPI Name for either of the “Net Sales”, “Tons” and “Bottles”) and a treemap displaying the Net Sales grouped by Customer Name (having the visual level filter KPI Name set to “Net Sales”).  

Now, when a rectangle from a treemap is clicked in order to achieve cross filtering of the KPI Values for that particular customer, the tile for Net Sales is displayed cross filtered correctly, but the other two display “blank”.

Investigating further I have noticed that when the rectangle is clicked, the context is altered not only by adding the Customer filter, but also by adding whatever visual level filter is set for the treemap (in this case KPI Name=”Net Sales”) causing the displayed value for the tiles to be blank (evidently because KPI Name cannot be both “Net Sales” AND “Bottles”, for instance, at the same time).

August 30, 2017 7:00 AM

Marco Russo (SQLBI) said:

Unfortunately you cannot avoid that propagation of the filter. I suggest you to submit an idea in Power BI dedicated forum to see whether it could be interesting also for other users, so MS can evaluate it.

September 8, 2017 3:38 AM

Stefano B said:

I have two related tables T1 and T2 with relation n to 1 on columns T1 [col_X] and T2 [col_Y]. I want to calculate the sum of a column in T1 without the detail level of column T1 [col_X]. If I try the following:


sum (T1 [col_Z]),

ALLEXCEPT (T2, T2 [col_Y]))

It works

But if I try the following:


sum (T1 [col_Z]),

ALLEXCEPT (T1, T1 [col_X]))

does not work.


Thank you

May 25, 2018 12:55 AM

Marco Russo (SQLBI) said:

What is the direction of the relationship? T1 one - T2 many or the opposite?

May 30, 2018 5:16 PM

Stefano B said:

Hi Marco, T1 many - T2 one. Thanks

June 1, 2018 2:35 AM

Marco Russo (SQLBI) said:

Maybe you want to use this:


sum (T1 [col_Z]),

ALLEXCEPT (T1, T2 [col_Y]))

June 7, 2018 6:32 PM

Leave a Comment


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



Privacy Statement