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 P3 Adaptive 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 P3 Adaptive 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…

0 Shares