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

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 4 Comments

  1. Cabby

    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. powerpivotpro

    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. Oxenskiold

    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. hexalm

    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 Comment or Question