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!
Yes, We’ve Seen This Image Before and I Am Sure We Will See it Again
Jump in the Wayback Machine…
In the Spring of 2011, I dove into a Power Pivot project that I thought was going to be simple, but even today remains the most complex thing I’ve ever done in DAX. I think it’s fair to say that the experience, at the time, was traumatizing. (The client’s business logic itself was/is incredibly complicated. It’s 100% legitimate, but I think barometric pressure might be factored into their budget/actuals ratios. Kidding.)
But like many difficult experiences, a lot of good came of it as well:
- I learned a ton – it forced me to advance my Power Pivot knowledge significantly
- It demonstrated to me that Power Pivot essentially had no “ceiling” – it could handle almost anything
- It became a Microsoft case study
- It “spawned” the GFITW.
Ah yes, the Greatest Formula in the World. The solution to all our custom calendar needs, and a pattern I’ve repeated hundreds of times since. On the blog, in the book, in client workbooks, everywhere.
Well it turns out, the GFITW could afford to go on a diet.
Um, Yeah. The First ALL Isn’t Necessary (But Doesn’t Cause Problems)
Here’s the “classic” GFITW pattern:
ALL(Custom Calendar Table),
FILTER(ALL(Custom Calendar Table),
logic to select a modified date range
In the past year a few people have asked me if that first ALL() highlighted above was necessary. But I never really had time to engage closely with those questions – the formulas were working, after all, AND I distinctly remembered absolutely needing the first ALL() back in the days of the BMD project.
Well, when I finally got time to go take a look, I couldn’t find a case where the first ALL() was needed.
Naturally, I looked harder. And still couldn’t find one. So I shelved the topic for a bit, and revisited it a few more times over the next couple of months trying to find some obscure past case where it was needed.
I’m prepared to come clean now and admit that I don’t think it’s ever needed
Now’s the Time When Rob Partially Defends Himself!
How to explain such a thing?
- The formula pattern was originally forged in the fires of Mount Doom, under duress, and at a time when I was “young” in the ways of the Force.
- The extra ALL() wasn’t causing incorrect results, and in fact was delivering incredible results, so there was never a reason to revisit that chapter of my “youth.”
- FILTER() is a subtractive function rather than an override function, so it was reasonably logical (but still wrong) to think that an ALL() inside of a FILTER() would only affect the operation of the FILTER(), and not the filter context “outside” of the FILTER().
- Each of the table functions in DAX (FILTER, ALL, DATESYTD, VALUES, etc.) has its own unique “impact” on filter context. Even if they return the same exact row set, they end up modifying the filter context in different ways. I spend a whole “module” on this topic in PowerPivotPro School, called “Table Purpose vs. Filter Purpose.”
With regard to #4, it is simultaneously accurate to say that DAX is somewhat “random” in this sense, AND that it’s beautifully designed to do exactly what you want 99.9% of the time. I think it’s beautiful. But don’t expect everything to behave by some grand unifying pattern, because that’s not actually what we need.
In fact I started a whole series of blog posts on this topic, called the Precedence Project, before realizing that I wasn’t going to end up with a nice clean table (I then abandoned the series). The interaction between the table functions and filter context is pretty damn nuanced, by necessity, and practically requires a per-function description rather than some overarching rule set.
Enough with the Sad Excuses!
Personal pride aside, I owed it to everyone to come clean on this one and share that the first ALL is not needed.
And I must also say thank you to the folks who took the time to point this out to me – unfortunately your names are lost in the sands of my inbox and the comment threads, but I truly am grateful (if you drop me a note or a comment and remind me, I will add your names here).
In PowerPivotPro School, I’ve already changed my teachings to reflect this, and in fact have refined some of my techniques even further as a result. Some of those refinements will undoubtedly appear on this site over time as well.
Anyway, until next time…