A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
In my last post I talked about a mistake I made early on in my DAX learning journey. In that post I showed a formula that used CALCULATE ( ) to turn a row context into a filter context (AKA context transition). Here is a quick refresh of the relevant part of that post.
Since this post, I have been thinking about CALCULATE ( ) and wondering how to explain “WHY” CALCULATE ( ) creates context transition – this is the topic of today’s post.
I am sure there is a wide age profile of readers of this blog, and at least some of you would remember the Merrie Melodies cartoon “Cheese Chasers” where the dog (Marc) is sitting and using an ACME adding machine to make sense of what he is observing. [I know I said Looney Tunes in the title, but there is not a lot of difference and I thought more people would know what Loony Tunes was]. Before you watch this brief 40 second clip from the cartoon, let me first set the scene. Everything is backward; the mice don’t eat cheese, the cat WANTS to be chased by the dog – you get the idea. Spoiler alert – I will refer to the punch line below, so don’t read on until you watch the video if you want the full 1951 immersive experience.
I don’t know what it is about this cartoon, but the punch line has stuck with me all my life ever since I first watched the show. “It just don’t add up” is a catch phrase that I use often, always thinking about this old cartoon, sometimes “miming” the data entry of the facts into an invisible adding machine prior to pulling an invisible handle as I declare “it just don’t add up”.
OK, what does this have to do with CALCULATE ( ) and context transition I hear you ask?! Well it occurred to me that the PowerPivot filter engine using the CALCULATE ( ) function is a lot like that old mechanical ACME adding machine. For those of you that don’t know how they work, basically there are several columns of buttons numbering 1 to 9. Each column represents one of the positions in the decimal number system, eg the 1s, 10s, 100s, 1000s etc. In the image to the right, the adding machine has 8 columns, meaning it can accept any number with up to 8 digits. So to enter the number 173, you would press 1 in column 3 (ie third from the right), 7 in column 2, and 3 in column 1.
Now here’s the point: it is not until you pull the handle that anything actually happens. So you can go ahead and enter any number in any of the columns. If you make a mistake, you can just change the digit to make it correct. One you are ready, you pull that handle on the right and the mechanical cogs in the adding machine crank over and do their stuff. And that is just how CALCULATE ( ) works – you pass inputs to the filter engine and then pull the calculate lever to activate the filter engine with the new inputs.
Now just to be completely clear, filter propagation happens automatically from the one side of the relationship to the many side of the relationship in DAX. There is no need to pull the calculate handle to get that to work – so I am not talking about that. However if you want to tell DAX to do anything over and above that automatic filter propagation (such as change an existing filter context in a Pivot Table or add a new filter to the existing filter context) then you need to pull the calculate handle to make it work.
As you would know, the CALCULATE ( ) function accepts one or more inputs as follows:
= CALCULATE ( expression , filter1, filter2, filterN …)
In the case of context transition, you are only using 1 input to CALCULATE ( ). You are simply wrapping the existing inner formula in a new CALCULATE ( ) function. Because the new CALCULATE ( ) function is inside the FILTER ( ) function, CALCULATE ( ) is effectively pulling the calculate handle on the filter machine for each iteration of the FILTER ( ) function, and it takes the current row context of each iteration of FILTER ( ) as the filter to apply to the engine.
So now when I see a CALCULATE ( ) function anywhere in DAX, I think of Marc sitting in front of that old ACME PowerPivot Engine and pulling the Calculate lever to re-run the filter engine with the new inputs passed to the engine; either row context inputs, a table, or anything else that acts as a filter for that matter. When you are writing your DAX in future and the result you get “just don’t add up”, maybe you have a row context and it is time to wrap that little sucker with a CALCULATE ( ) function run the filter engine with each iteration.
As a final note on the topic, you may have read Marco Russo’s comment on my last post explaining that you can (indeed should) use SUMMARIZE ( ) and/or pass a table to the CALCULATE ( ) function, and the existence of related records in that table will also act as a filter. Marco also posted a link to this TechEd video explanation by Alberto Ferrari explaining in detail how it all works. I highly recommend watching this entire video if you want to get a deeper understanding of the the many-to-many relationship pattern that relies on CALCULATE() re-running the filter engine with the new inputs along with how and why it works (except for the ‘black magic’ which remains a mystery). I have to say I am now pumping out ‘black magic’ many-to-many DAX formuale without even thinking now – it is very empowering!
Matt Allington is a PowerPivot Consultant and certified powerpivot(pro) University trainer at Excelerator BI in Sydney Australia.