DAX – The Correct Usage of EARLIER()

Egg on My Face For Improper Use of EARLIER()

Tonight I was looking at one of my recent posts, the one about “fuzzy” time relationships in calculated columns, and I discovered that I had used the EARLIER() function in places that I did not need to.

OK, well, it’s not a LOT of egg.  I mean, my formulas were all returning the correct answers.  They were just needlessly complex.

Here was the calculated column formula in question:

=CALCULATE(MAX(Events[EventType]),
   FILTER(Events,
          Events[RatID]=EARLIER(Sniff[RatID]) &&
          Events[SessionID]=EARLIER(Sniff[SessionID]) &&
          Events[PostTimeID]>=EARLIER(Sniff[TimeID]) &&
          Events[PreTimeID]<=EARLIER(Sniff[TimeID])
         )
)

Every one of those highlighted EARLIER() functions is 100% unneeded.

If I remove all of the EARLIER()’s, the formula returns the same result.

=CALCULATE(MAX(Events[EventType]),
   FILTER(Events,
          Events[RatID]=Sniff[RatID] &&
          Events[SessionID]=Sniff[SessionID] &&
          Events[PostTimeID]>=Sniff[TimeID] &&
          Events[PreTimeID]<=Sniff[TimeID]
         )
)

And here’s the proof that the EARLIER()’s were NOT needed:

Why Weren’t They Needed?

Let’s look at the FILTER() in my formula, and focus on just one of the comparisons:

FILTER(Events,
   Events[RatID]=Sniff[RatID]
   …

I am filtering the Events table to rows where the RatID is the same RatID in the Sniff table.

And this calculated column is in the Sniff table.  And there is not relationship between the Events and Sniff tables.

That FILTER() is quite straightforward then – it looks at the RatID in the current row of the Sniff table (since this is a calc column in the Sniff table) and then goes and finds rows in the Events table that have the same ID.

EARLIER() is only needed when you are “jumping back out” of something.  And there isn’t anything to jump back out of here.  Let’s revisit an example of where EARLIER() IS needed:

Flashback:   Simple Use of the EARLIER Function

This is an excerpt from a post last month, “reprinted” here for convenience.

Say I have the following VERY simple table like this:

And I want to add a third column that is the total for each customer:

The calc column formula for that third column is this:

=CALCULATE(SUM([Amt]),
           FILTER(ALL(‘Table’),
             ‘Table’[Customer]=EARLIER(‘Table’[Customer])
                 )
          )

Note the highlighted part:  in that formula we are filtering on ALL(Table) rather than just the “raw” Table.  Here’s the crux:

When I say FILTER(ALL(Table)), all of my references to columns in Table will have “forgotten” all notion of “current row” and will instead be references to the entire column.  That is because of the ALL().

So the EARLIER() function is my escape hatch that allows me to go back and inspect the current row’s value.

This line of the formula:

  ‘Table’[Customer]=EARLIER(‘Table’[Customer])

Can be understood as:

  ‘Table’[Customer]=CurrentRow(‘Table’[Customer])

EARLIER is Probably Best Understood as CURRENTROW

In fact that’s a better name for EARLIER 99% of the time.  Just think of it as a CURRENTROW function, useful only in calculated columns, and only when you are performing FILTERS on ALL(Table), when you need to “jump back out” of the ALL and fetch a value from the current row.

Yes, it IS useful in other cases.  But I suspect that those other cases are rare enough that rather than a general purpose function like EARLIER, we should have been given a dedicated, easy to understand version named CURRENTROW.  Or maybe we should have both.  Yeah, both.  That would be good.

Why My Formula Didn’t Need EARLIER

Quite simply, I had no ALL() to “undo.” 

FILTER(Events,
   Events[RatID]=EARLIER(Sniff[RatID])
   …

I was just filtering on the Events table.  Not ALL(Events). 

And even if I had been filtering on ALL(Events), that wouldn’t have impacted the Sniff table, WHICH IS WHERE THIS CALC COLUMN LIVES.  So even ALL(Events) would NOT have required me to use EARLIER(Sniff[RatID]) to get the current row from Sniff – a simple Sniff[RatID] is sufficient.

Whew.  Glad I caught this before anyone noticed. 

But I suspect some people did, and were just polite