skip to Main Content

power pivot to power bi

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! Smile


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:

  1. I learned a ton – it forced me to advance my Power Pivot knowledge significantly
  2. It demonstrated to me that Power Pivot essentially had no “ceiling” – it could handle almost anything
  3. It became a Microsoft case study
  4. 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:


CALCULATE(original measure,
          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 Sad smile

Now’s the Time When Rob Partially Defends Himself! Smile

How to explain such a thing?

  1. imageThe 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.
  2. 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.”
  3. 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().
  4. 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…

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 4 Comments
  1. I am wondering if the additional, somewhat not completely actually necessary but still not looking out of place all() does change the way the measure is internally calculated, i.e. does it affect performance or not?

  2. First off, total LOL at ” the additional, somewhat not completely actually necessary but still not looking out of place all()” 🙂

    Second, yes, it is certainly possible that it impacts perf. But we’d have to ask MS or run a trace in order to know for sure. And then the next question is “how MUCH impact?”

    In some ways it would be GOOD news if the impact were significant, since all of our custom cal measures could be made faster with one simple change.

  3. Hi Rob,

    Thank you very much for your blog messages on Power Pivot and excel 2013 standalone. They were a tremendous time saver. Besides that I really like the humorous way you present technical topics.

    You wrote:

    3. 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().

    An ALL() inside a FILTER() does NOT directly affect the filter context outside the FILTER(). It is the result of the FILTER() that affects the filter context. It returns a table with all of the columns from the calendar table (with the filtered rows). That result table blocks/overrides/overwrites all columns from the calendar table in the original filter context from when CALCULATE was called (if the calendar table was part of the original filter context). The redundant ALL() also blocks the same columns that’s the reason it is superfluous.

    However whether you use the explanation in your text or the one I just described the result would be the same. So in effect it’s just a matter of words.

    The singleton ALL() ONLY blocks the columns from the calendar table, it doesn’t return a table hence it will have no impact on performance whether it is there or not.

    A little word game teaser on ‘subtractive’: which rows does the following FILTER() return? FILTER(ALL(calendar),true)

  4. Ha! No sooner than I wondered, “are ALL(ALL()) in those formulas necessary?”, I scrolled down the page I had open and found this post.

    In fact, the persistence of ALL() has been a thorn in my side for a week now on some work I’m doing.

Leave a Reply

Your email address will not be published. Required fields are marked *