The CALCULATE function in DAX is the magic key for many calculations we can do in DAX. However, it is not pretty intuitive how it works and I spent a lot of time trying to understand how it can be used.

First of all, this is the syntax.

CALCULATE( <expression>, <filter1>, <filter2>… )

The *expression* that we put in the first parameter has to be evaluated to return the result (that is a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.

This expression is evaluated in a context that is modified by the filters in the following parameters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following table is the one we import in PowerPivot in a table named Demo.

If we project the count of Rows in a PivotTable putting the Name on the Rows, we get the following result:

Now, we might have the need to calculate a measure which is not affected by the selection of the Name or, in other words, which always calculate the context for all the names. Thus, we define this calculated measure:

CountAllNames = CALCULATE( COUNTROWS('Demo'),

ALL( 'Demo'[Name] ) )

We obtain the CountAllNames column in the PivotTable that always returns the number of all the rows of the Demo table, without considering the filter on the Name.

However, if we add Color attribute to the rows of the PivotTable, the CountAllNames is filtered by that attribute too. In fact, for each color, we have 2 rows for a Name (see Count of Rows column) and 6 rows considering all the names (see CountAllNames column).

Now, we can also add a Boolean expression as a filter of the CALCULATE function. For example, we might filter just the Car transport.

CountAllNamesCar = CALCULATE( COUNTROWS('Demo'),

ALL( 'Demo'[Name] ),

'Demo'[Transport] = "Car" )

In this case, we will reduce the CountAllNamesCar column for a color of Emily to 3, because the number of rows with color Green and Car transport are 3.

At this point we might wonder whether the Calculate filter parameters are enlarging or restricting the context of evaluation. The rule is the following one.

If the current context has a filter on a column of a PowerPivot table (which is a selection of a PivotTable, regardless it is a slicer, a report filter or a row/column selection), any *reference* for that column in one or more filter parameters of the Calculate function replaces the existing context. Then, the filters specified in the CALCULATE parameters are combined together like they were in an AND condition of a WHERE clause of a SQL SELECT statement.

For instance, consider a filter on the Color green using a Boolean expression in the CALCULATE function:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

'Demo'[Color] = "Green" )

**A Boolean expression** used as a filter parameter in a CALCULATE function **corresponds to an equivalent FILTER expression** that operates on all the values of a column (for this reason, you can only a single column can be used in a Boolean expression that is used as a table filter expression):

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

We obtain that ColorGreen column always filters by color Green and each Name has only 2 rows with color Green.

Any filter expression in a CALCULATE statement overrides the existing selection of the PivotTable for the columns it contains. In the previous rule we highlighted the “reference” definition, because the FILTER that is internally used in place of the Boolean expression uses a FILTER expression that returns a set of values for the Color column. Thus, the existing selection for the color (the color is in fact specified in the rows of the PivotTable) is overridden by our filter and only green rows in the source table are considered to calculate the ColorGreen measure value. The true reason we lose the current selection on the color attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and ignores the existing selection.

If we don’t want to lose the existing selection of the PivotTable (that means that we don’t want to lose the existing filters on the calculation context), we can simply use in the FILTER expression a function that doesn’t ignore the existing selection. Instead of using the ALL( Demo[Color] ) expression as the source of the filter, we can use the VALUES( Demo[Color] ) expression, which keeps existing selections and returns the values still available in the color attribute.

In fact, if we use the following calculated measure:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **VALUES('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

We obtain as a result that the Color filter in the PivotTable is still active, and returns no rows for all the colors but green.

We can see that the FILTER expression in a CALCULATE function *always replaces* the previous context for the referenced columns. However, we can save the existing context by using an expression which uses the existing context and further restricts the members we want to consider for one or more columns. And this is what we have done using VALUES formula instead of ALL as the first parameter of the FILTER call.

Thanks to Marius Dumitru, the various combination of FILTER, ALL, VALUES in a CALCULATE statement can be summarized in this way.

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

-- ignores/replaces existing Color filters and sets a filter on Green

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **VALUES('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

-- keeps existing Color filters and adds a further filter on Green

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ),

** VALUES('Demo'[Color])** )

-- same as the previous expression (keeps existing Color filters)

-- NOTE: the first filter would consider all the colors, but the second

-- expression (VALUES) only consider the current selection and

-- the two filters will be considered using an AND condition, thus

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo')**, 'Demo'[Color] = "Green" ) )

-- ignores/replaces filters on all Demo columns,

-- and sets a filter on **all** columns (Name, Transport etc., not just Color)

-- with rows that meet the condition

Finally, some words of caution have to be spent to the first parameter we pass to the FILTER function. If we consider this expression:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **'Demo'**, 'Demo'[Color] = "Green" ) )

We pass the whole Demo table to the FILTER condition, which results a filter of the current context with **all the columns**! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows. What does it mean?

Well, consider a further selection on the PivotTable where the Transportation attribute is filtered by Bike. This is the result using the ColorGreen definition we have just defined.

Now, let’s add another filter to the CALCULATE function, filtering also the rows with Trasport equals to Car.

CarGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

**'Demo'[Transport] = "Car"** )

which, as we have seen before, corresponds to:

CarGreen = CALCULATE(

COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

**FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" )** )

The results is that the filter for Bike defined in the PivotTable plus the filter for Car defined in the CarGreen measure returns no rows at all in the CarGreen result. If we think about it, why this happens is not very intuitive at this point!

Our filter on the Transport column is actually restricting the current selection and is not replacing it! But, wait, if we write just the filter on Transport, without the filter on Color, we have:

TransportCar = CALCULATE( COUNTROWS('Demo'),

**'Demo'[Transport] = "Car"** )

that produces the following result, which replaces the Bike selection of the PivotTable in the TransportCar column!

This last calculation (TransportCar) is simple to explain, because it has the same behavior we have seen before with the first ColorGreen calculated measure, where the filter of the color green replaced any existing color selection in the PivotTable. The difference in the CarGreen calculation is the other filter parameter, which returns all the columns from the Demo table. Consider the highlighted filter in the CarGreen formula:

CarGreen = CALCULATE(

COUNTROWS('Demo'),

**FILTER( 'Demo', 'Demo'[Color] = "Green")**,

FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The filter on color green returns *all the columns* of the current context. If we consider the corresponding rows for the cell B5 of the PivotTable (Emily, Green, Bike), this is just one row (the yellow one), and this row has the Bike value for the Transport attribute. When we apply the second filter, we have a single value for the attribute Transport, which is Car. At this point, the intersection between those two sets of Transport (one is only Bike, the other is only Car) is an empty set. Thus, the result for CarGreen measure is empty, because there are no corresponding rows for the selection made.

This can be tricky, but we finally have this behavior.

- The CALCULATE function applies a calculation (the first parameter) for each cell, considering the resulting context by applying the filters (the second and following parameters) to the current context.
- Each filter can have values for one or more columns.
- Each column is computed individually in the filters expressions of the CALCULATE function
- If a column value is specified in at least one filter, it replaces the selection of the current context for that column.
- If a filter expression returns more columns, each one has its own independent set of values in the final calculation context definition
- If a column is specified in more filters, the resulting values are the intersection of these set of values (for that column).
- After all the filters have been evaluated, the intersection of the column values determines the calculation context for the expression passed as the first parameter to the CALCULATE function

Despite its complexity, this calculation is pretty fast. The key point is to understand all the side effects we have when a filter returns more columns than those we specified in the filter condition itself, which is something we have to consider carefully each time we use one or more FILTER functions inside a CALCULATE expression.

## Comments

## Kasper de Jonge said:

Great post Marco, keep m comming :)

## SQLBI - Marco Russo said:

PowerPivot supports only one type of relationship between two tables, which is the one-to-many relationship.

## sam said:

Is there a way to add a measure that will show a 1 against the first instance of a particular name or name color combo

So in your example

Name Flag

Emily 1

Kimberly 1

Emily 0

Victoria 1

Kimberly 0

Victoria 0

Emily 0

etc

## Marco Russo (SQLBI) said:

Sam,

the problem here is what is the definition of "first instance". Probably, you want to refer to the physical order of data. Can you be more explicit? Do we have some constraint to look for a solution?

Marco

## SQLBI - Marco Russo said:

When you use CALCULATE in DAX you are creating a new filter context for the calculation, based on the

## Martijn Muilwijk said:

Marco,

Is there any scenario you can think of in which you would prefer using the FILTER() statement over the CALCULATE() statement? In other words: It is always possible to achieve the same functionality with the help of CALCULATE() when using FILTER()

## Marco Russo (SQLBI) said:

Usually no, but FILTER has to be used when you have to pass a parameter to CALCULATE.

In fact:

CALCULATE( <expression>, <filter1>, ... <FilterN> )

every <filter> parameter is often the result (implicit or explicit) of a FILTER statement.

## Craig said:

Just want to say "thank you" for the great article after many hours of frustration.. with your help ive finally worked out the filter function:) next step.. buying the book!

## Max said:

Hi Marco,

Can you suggest a clean way to use Calculate with a conditional filter. This was easy in MDX using SCOPE but I've not yet figured it out in DAX.

Say for example you want a total for every year except for the current year you want to use the previous year total.

MDX

CREATE MEMBER CURRENTCUBE.[Measures].[Best Estimate Claims] AS ([Measures].[Claims]), VISIBLE = 1 ;

SCOPE ([Measures].[Best Estimate Claims],[Year Of Account].[Year Of Account].&[2012]);

THIS = ([Year Of Account].[Year Of Account].PrevMember,[Measures].[Claims]);

DAX

[Best Estimate Claims] = CALCULATE(SUM(DFR[Claims]), PARALLELPERIOD(YearOfAccount[FirstDayOfYear],IF(MIN(DFR[YOA])=2012,-1,0),YEAR),ALL(YearOfAccount))

(A relationaship exists between DFR[YOA] and YearOfAccount[YearOfAccount])

The two expressions above are equivalent in results but the DAX feels like a bodge

## Marco Russo (SQLBI) said:

You don't have something like SCOPE in DAX - you might write the same DAX expression in different ways, for example by moving the IF before the CALCULATE, but at the end it would not change that much.

MDX is more advanced, DAX is simpler (even if powerful) and has a different approach.

## Frank Tonsen said:

Hi Marco,

with regard to your book (pages 196-198)

maybe I am wrong, but IMO

CALCULATE(SUM(Orders[Quantity]),Orders[Color]="Green")

overrides color context

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

keeps color context, or to be more precise kills color context and then restores it

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

keeps all context, or to be more precise kills all context BUT THEN ALSO RESTORES IT.

THEREFORE, naturally the measure

CALCULATE(SUM(Orders[Quantity]),Orders[Color],Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

## Frank Tonsen said:

Unfortunately I made a typing error in the last paragraph, of course the second parameter of the CALCULATE function has to be Orders not Orders[Color]:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

## Marco Russo (SQLBI) said:

Frank,

the parameters you put in a CALCULATE call are all put in an AND condition, so

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

replaces existing filter on Color, but the two parameters will be put in AND together - so at the and the filter on Color has now values if the existing selection didn't include Green, or is just Green if existing selection included Green.

In case of

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

you have the same result, but in this case Orders is like VALUES( Orders ), and gets all the rows selected in Orders instead of just a selection for one column - so Green filter always wins.

The last expression

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small")

returns no rows at all if you have a filter on Large, because the Orders parameter contains the current selection of Orders rows, which are only those Large (according to slicer selection) and the filter on Size equal to Small produces an empty result.

Sounds good?

## Frank Tonsen said:

Marco,

thanks for your quick answer. I guess it confirms my understanding.

Thus, regarding CALCULATE there is actually no point in using the facts table as a parameter because

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

is the same as

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

Right?

## Marco Russo (SQLBI) said:

No, they are not the same.

If you have

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

and you had a selection of Color = "Red" in the PivotTable, such a selection is replaced for this formula with Green and you can evaluate a different set of rows in Orders.

When you have:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

then the initial selection is "copied" in Orders, and at this point the replacement of the Color selection with Green is still not enought to go outside of the current filter context, because the copy of Orders in the parameters of the CALCULATE makes a copy of the initial selection (i.e. Red) translated to the Orders table, and the result is an empty set in this case.

Marco

## Frank Tonsen said:

Marco,

now I got it.

Many thanks for your explanations.

## Gunshy said:

Hi,

Need help.

How can I conditionally calculate a table? for example, If Table[SourceType]="SALES" then SUM(Table[Quantity]) else CALCULATE(Table[Quantity],DATESBETWEEN(DateTable[Date],DATE(1900,1,1),LASTDATE(DateTable[Date]))) ?

Thanks

## Marco Russo (SQLBI) said:

Gunshy,

if you mean that you want to use the selection made by the user, you should use something like:

IF (

HASONEVALUE ( Table[SourceType] ),

IF (

VALUES ( Table[SourceType] ) = "SALES",

SUM( Table[Quantity] ),

CALCULATE(

Table[Quantity],

DATESBETWEEN(

DateTable[Date],

DATE(1900,1,1),

LASTDATE(DateTable[Date])

)

)

)

)

## Oleg G said:

Wow! I just spent several hours re-creating this blog entry in Excel, and between this and last week's PowerPivot Workshop, I think I finally got it! Now it suddenly seems very easy and intuitive!

## Marco Russo (SQLBI) said:

After a few years, I still think it is not intuitive! But it can be easy after you get used to it. :-)

## Leshi said:

Hi Marco,

If I want to filter on multiple values such as Product ID in (123,456), how can I use IN statment in my filetr within CALCULATE measure...i.e. I am trying to acomplish something like this

CALCULATE(SUM(SALES),PRODUCT_ID=123,456) ...Is there an IN clause or something similar?

## Marco Russo (SQLBI) said:

Leshi, unfortunately there is no IN statement in DAX. If you store the list of products in another table, you might use the CONTAINS function in DAX.

## Leshi said:

Thanks Marco,

After tinkering around a little, I realized I could use an OR statement (||) if one to filter by few values instead...

Thanks for your feedback and keep up the good work

Leshi

## Marco Russo (SQLBI) said:

Oh yes, the OR statement is good too, it's just longer to write if you have many items to check! :)

## BI Analyst said:

Hi Marco,

Thank you for your tutorial. I have a question regarding the example toward the end of this tutorial.

In

CarGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

'Demo'[Transport] = "Car" )

The measure returns no rows in the pivot table.

My understanding (or logic) is that because a transport cannot be a bike and a car at the same time.

But in your next example which you removed the Color filter

TransportCar = CALCULATE( COUNTROWS('Demo'),

'Demo'[Transport] = "Car" )

My logic is no longer holding because the measure column in the pivot table is not empty.

Could you help to explain (in the simplest way possible) why the one with Color filter return empty values and the one without Color filter return non-empty values?

Thanks again in advanced!

BI Analyst

## Henk Hofmans said:

I have got two tables with a commen key field. The tables cannot be related because there are duplicate values on both sides. In table "A" I create a new column that should count the number of occurences of "A.key" in the "key" column of table "B".

This is what I have tried, but doesn't work:

=calculate(COUNTA(A[Key]);B[Key]=A[Key])

How to solve this?

## Marco Russo (SQLBI) said:

You should write this:

= CALCULATE ( COUNTROWS ( A ), FILTER ( ALL ( A[key] ), A[key] = B[key] ) )

## Luke said:

Great post - very useful.

Does anyone know if there is a way of removing the current filter of a pivot table, but still keeping the row filters? For example, I'm trying to be able to filter a pivot table down on a particular group, but have one average across all the data with the same row splits.

Cheers

## Luke said:

Looks like the answer to my question is fairly straightforward - for ignoring the filter on a Pivot Table, but keeping the row/column filters you can just use CALCULATE(Expression,ALL(FilterColumn)

## Anand said:

Great Man. That is why I am Ur fan Marco.

I was trying to buy ur book on Data Modelling in Powerpivot for excel 2013.

But I was unable to get the e-Version.

## Marco Russo (SQLBI) said:

You can get the Kindle version on Amazon or other formats on oreilly web site. Let me know where you looked at.

## Rekha said:

Hi Marco,

I have 7 tables and each one is having unique column.e.g.I have table A with NRIC column as unique.Other table as B having Investigation ID as unique column with NRIC as a foreign key. All tables are some what related to each other.

When I try to create pivot table I come across the below scenario:

when I drap NRIC from table A and want to display related Investigation ID from table B, it shows me incorrect result.I mean one NRIC gets associated with all Investigation IDs.

I am new to Power Pivot Table.Please suggest how should I go about this.

Thanks

## Marco Russo (SQLBI) said:

Rekha,

from your description, it seems that you didn't create relationship between tables in the data model. Go in the Diagram View of the Power Pivot window and check that relationships are correctly defined between tables.

Marco

## Rekha said:

Thank for quick reply Marco.

I have created relationships between tables, but result is not as per our expectations.

For each NRIC from table A, it should return only those associated Investigation Ids from table B.

Am I missing anyhting?

## Marco Russo (SQLBI) said:

Rekha,

have you put a measure in the pivot table? If you don't have a measure, hierarchical relationships between different tables are not automatically identified by the Pivot Table in Excel.

Marco

## Rekha said:

Can you help me in this, as I am completely new to this?

Thanks

## Marco Russo (SQLBI) said:

Rekha,

I suggest you looking at the help or some presentation - look at how you create a pivot table starting from a model with more than one table. You can also look at some of our books:

http://www.sqlbi.com/books/

## Rekha said:

I was looking for some samples.

Thank you so much Marco for your help.

## Malcolm said:

Can calculate function together with a Contain function. eg. Calculate(sum(table1),contain(table1,col1,"ast_")).

I have purchased the the book Microsoft Excel 2013 - Building Data Models with PowerPivot written by you and couldnt find a solution in that

Thanks

## Marco Russo (SQLBI) said:

It's not clear what you want to do. Contains is a sort of "lookup", which might be not what you want to do - it seems that you want to do just this:

CALCULATE ( SUM ( table1[col2] ), table1[col1] = "ast_" )

But if you want to do a sort of "like" statement, then you have to write:

CALCULATE ( SUM ( table1[col2] ), SEARCH ( "ast*", table1[col1], 1, 0 ) > 0 )

Check this article for more info about string comparison in DAX:

http://www.sqlbi.com/articles/from-sql-to-dax-string-comparison

## Malcolm said:

Yes I wanted a like statement and got hold of your thread after I left a comment. But still could'nt solve it. Figured out the reason to be the * mark. Thanks Marco.