Guest Post by **Scott Senkeresty**

### Intro from Rob

*Hey, it starts out simple and powerful: CALCULATE is the SUMIF you always wished you’d had. It works in pivots. It’s the “anything IF.” It’s amazing, really, how many doors it opens.*

*Of course, CALCULATE is designed to be powerful in ways we can’t even IMAGINE in our first day/week/month of using it. You can spend years discovering all the things it can do – and that’s a good thing! But sooner or later you’re going to hit something with CALCULATE that makes you scratch your head – why is it returning THOSE results?*

*I myself entered this twilight zone with the Precedence Project – a series of posts that I quickly abandoned. It turns out that, practically speaking, you don’t need to achieve deep theoretical understanding of this stuff in order to achieve great results.*

*Below, however, Scott does a great job of resolving those mysteries. And he does so by “channeling” two old friends who live at the base of the Alps. Take it away, Scott…*

### Going to “Graduate School”

All right, so I’ve read Rob’s book a few times. (Heck, I am credited as tech editor on it.) I’ve devoured PowerPivotPro University. So now what, I ask Rob?

“Go forth and conquer – data is your ocean,” is his answer. He’s a practical sort of guy. Me, though? I’m never satisfied until I’ve completely torn the machine apart.

So, as I hinted in my last post, I went to graduate school and spent a few *intense* days engrossed in Marco and Alberto’s book.

I had to read and re-read the chapter on CALCULATE() three complete times, and for some paragraphs, probably closer to a dozen times. But then it happened. I became one with CALCULATE.

So, in the post, I am not really giving **new** info. Rob does a great job of the material in his book, again from that practical perspective. The Italians obviously do a great job. There are blog posts from Rob and others that discuss it as well. But this stuff is so fundamental to your understanding of DAX, I think it is worth retelling again. I know for me, I had to read different material again and again before I was one with the Matrix. So maybe my retelling will help you too.

### The Boolean Case

**CALCULATE**() is simply the way we execute an expression in a modified filter context. The nerdy looking syntax: CALCULATE(<expression>,<filter1>,<filter2>…)

Let’s jump into an example. You already have this measure against your sales data.

[Avg Sales] := SUM(Sales[ExtendedAmount]) / COUNTROWS(Calendar)

We drop it on a pivot table broken out by “IsWeekend” and “DayOfWeek” and get something like what you see on the right.

We see that Saturday has our highest average sales, and decide we want to start building out a new measure… say [% of Saturday]. As a step in that direction, we pull out CALCULATE() and get to work!

[Sat Avg Sales] := CALCULATE([Avg Sales], Calendar[ShortWeekdayName] = “Sat”)

Before we look at the results, let’s review the “rules of calculate”.

- If a filter parameter is against an
*existing*filter context, it replaces the existing filter. - Otherwise, a
*new*filter is added to the filter context.

Since our pivot table is split out by ShortWeekdayName (that is “on rows”), our filter parameter is just going to **replace** that filter with ShortWeekdayName = “Sat”. Right?

Well, Yes! That is exactly what happened! What we didn’t mention is that IsWeekday is **also** in the filter context, and we made no effort to replace *that* filter… so while the expected value of $26,319 shows up in both rows where it’s the weekend… we just get blanks for weekdays.

Well, that’s easy to fix…

=CALCULATE([Avg Sales], Calendar[ShortWeekdayName] = “Sat”, **ALL(Calendar)**)

We simply add a second filter parameter to CALCULATE (you can pass as many as you like), this time replacing the filter context on Calendar. We could have just as well passed ALL(Calendar[IsWknd]) for the same impact on *this* pivot table, but it would break again once somebody drops Year/Quarter/etc on a row, column or slicer… so, we just strip away the filter on the whole darn table. We are crazy like that.

Note this is a different “style” of filter parameter to calculate. We are not passing a **boolean** expression, we passed it a **table. **That means I am typing in the wrong section and better move along…

### The Table Case

Recall there are restrictions when using boolean parameters to CALCULATE(). For example, you can not reference more than one column (as the engine would get confused on which filters to remove). These restrictions (which allow for great performance benefits) are removed by passing a **table** as the parameter. When you pass a table, you are explicitly telling the engine “Hey DAX engine for this filter, here are all the rows I want you to use. No more, no less”.

We already saw ALL() used above, where we said “Hey DAX, clear off any filters from the Calendar table”. Another common usage is passing the results of FILTER(). What I want to do is compare the same logic we used above (in the boolean case)… but do it in the table case via FILTER().

=CALCULATE([Avg Sales], FILTER(Calendar, Calendar[ShortWeekdayName] = “Sat”))

Well, that’s… interesting. We have the same problem with IsWeekend, but now we have a new problem… there isn’t even a value for Sunday!?

Here is the thing. You see that Calendar parameter to FILTER? It is still under the influence of the **original filter context.** When we are evaluating Sunday the filter context is { IsWeekend=1, ShortWeekdayName=”Sun” }. From that set of rows, which of them will evaluate Calendar[ShortWeekdayName] = “Sat” to true? None of them! So, you get a blank for that Sunday row.

Okay… what if I add another filter parameter to calculate?

=CALCULATE([Avg Sales], ALL(Calendar), FILTER(Calendar, Calendar[ShortWeekdayName] = “Sat”))

Will that help us? It certainly “reads nice”, but no. The result would look **identical**.

We have created a new filter context (with ALL) that strips filters off the Calendar table, but we **still** have the *other *filter where ShortWeekdayName must be Saturday. **Both** filters are applied. That is how CALCULATE works… every filter parameter is added to the filter context and basically AND’ed together. They don’t interact in **any** way.

If you do something silly like pass two filters, one where WeekdayName is Sat, and one is WeekdayName is Sun… you simply get no rows, because no row is **both** Sat and Sun.

The solution here is to move that ALL() to surround the first parameter to FILTER().

[Filter All Sat Avg Sales] := CALCULATE([Avg Sales], FILTER(**ALL(**Calendar**)**, Calendar[ShortWeekdayName] = “Sat”))

Then we happily get rows everywhere. Let’s step through this a bit.

- CALCULATE() is going to execute our expression [Avg Sales] for each row. On each execution the filter context will be different (each of the combinations of IsWeekend and ShortWeekdayName).
- The filter context from 1,
**flows into each of the filter parameters passed to CALCULATE().** - Each of the filter parameters to CALCULATE() then modifies the filter context in the same way as the boolean case. If the current filter context already has a filter on the column, it replaces it, otherwise it adds a new filter.
- In our case, FILTER() accepts the filtered (say Weekend=Sun, Weekend=1) then nukes it by calling ALL(), then applies the new filter [ShortWeekdayName] = “Sat”… and as a result returns this new
**table**back to CALCULATE(). “Hey DAX, for this filter, here are the rows I want you to use. No more. No less”.

Note that instead of FILTER(ALL(Calendar)… we could have passed FILTER(ALL(Calendar[ShortWeekdayName]) … and the results would be the same as the 2nd pivot table in this post. We would not have cleared the filter on IsWeekend, so Mon-Fri would be blank rows.

### Conclusion

For me, this all clicked when I formed the mental model that **for each filter parameter to CALCULATE(), a filter context flows in, and then flows out.** In my head there are little arrows flowing into the parameter, then flowing back out. That mental image aids my understanding that my parameters (frequently a call to FILTER()) are always impacted by the **existing** filter context, but then my parameter can add/clear/change however it wishes before returning the filter context to CALCULATE().

If you keep that in mind, along with the understanding that all filter params to CALCULATE() will be AND’ed togther… you too will be one with CALCULATE()!

Is there any performance difference between

=CALCULATE([Avg Sales], Calendar[ShortWeekdayName] = “Sat”, ALL(Calendar))

and

= CALCULATE([Avg Sales], FILTER(ALL(Calendar), Calendar[ShortWeekdayName] = “Sat”))?

In other words, is there any compelling reason to select one over the other, or is it just a matter of style?

When you say “Filter Context and Arrows in, then Arrows Out” is it equally okay to just think of it as “AND Criteria” where all conditions must be TRUE?

Note: Just in case you try to copy formulas from the blog (to save some time), if there are double-quotes in the formula, you’ll need to (I had to) replace the copied double-quotation characters with ones you type in from your keyboard.

Could be a country-code setting, but it always seems to catch me off-guard.

@Scot

Could you kindly explain how the filters work in the below measures

The Data is the Adventure Works Database

The Task is to find Sales from thoose customers who visited 2 or more times

1) [mSales] = SUM(Sales[ExtendedAmount])

2) [mOrders] = DISTINCTCOUNT(Sales[SalesOrderNumber])

3) [mSalesCustRepeatMorethan2] =CALCULATE([mSales],FILTER(Customers,[mOrders]>=2))

In the 3rd Measure are we not using a Measure defined on the Fact table to filter a LookUp Table -i.e are the filters not moving uphill.

I asked this to question to Rob as well – but could not get response

However the measure below also works and I do understand how

mSalesCustRepeatMorethan2] =CALCULATE([mSales],FILTER(VALUES(Sales[CustomerKey]),[mOrders]>=2))

Sorry the last couple of line should read

“So is the filter context IS NOW travelling up (from Sales to Customer) and then Travelling down (Customer to Sales) !!! – Like a Circular Reference !!!”

Any chance of getting a copy of the workbook with this example? Thanks.

Hello. Working with calculate, is it possible to reference a measure in the filter arguments so it changes when the measre results changes ? or do you have to use a filter function for that.?