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


 
image

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:

image

Old Formula, Fixed Formula,
and Comparison Column That Proves They Are Equivalent

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:

image

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

image

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 Smile

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 17 Comments
  1. Hi Rob, if I remove the “ALL” from formular=CALCULATE(SUM([Amt]),FILTER(ALL(‘Table’),‘Table’[Customer]=EARLIER(‘Table’[Customer])), I would get the same result using your simple sample data.Is the “ALL”in your formular really needed?

  2. I do not think the “ALL” is needed here. “All”is only needed if you need to remove all filters from your table.

  3. Using SUMX(FILTER(‘Table’,’Table'[Customer]=EARLIER(‘Table'[Customer])),’Table'[Amt]),would give us the same result.

    1. Bobby, if you would like to send me a copy of the workbook you are using I will credit you with it in the next post. We don’t use calc columns much at all at Pivotstream, and I’m short on time these days. But I would very much like to get to the bottom of all of this 🙂

      Send me an email if you would like to cooperate on this.

  4. Rob, I’m not sure why you’re using FILTER to begin with. The CALCULATE function takes filter expressions as parameters. Why not just use:
    =CALCULATE(SUM([Amt]),ALL(Table1),Table1[Customer]=EARLIER(Table1[Customer]))

    Now, if you choose to use FILTER (an iterator) then inside the FILTER function it forgets your row context so essentially the ALL is implied. Perhaps this explains the results noted by Bobby.

  5. I have a question regarding YTD. My spreadsheet has 2 pivot tables. The 1st shows the current month activity based on a slicer selection. The 2nd shows the same activity YTD. But it always shows all the data loaded for the current year. I want to show the YTD as of the month selected for the 1st pivot table. I’d prefer not to ask the user to select using another slicer but rather to filter in DAX. Is this possible?

    1. Without using VBA, there is no way I know of to have one pivot impact another, if that is what you are asking – sorry.

      1. When I was trying to using ALLEXCEPT, I initially thought it presents all values except the conditions provided, instead of filtering them. Possibly naming it to CHOICE or something like that. The formula that I used can be used to countif based on 2 conditions/filters. I got here through another post looking for a solution. My solution dont seem relevant to this thread by anyways :).

  6. I woke up in the middle of night and started reading DAX documentation as bedtime reading.
    I came across an article explaining DAX contexts – Row vs Multi-row context. There was an example very similar to yours with use of EARLIER but I couldn’t understand why they use this function if I did similar things exactly without it… [CurrentRow_FieldKey]=SomeOtherTable[FieldKey]. Good to know that even Microsoft documentation is wrong and not that I’m insane…

    The “incriminated” article is at

    https://support.office.microsoft.com/en-us/article/Context-in-DAX-Formulas-2728fae0-8309-45b6-9d32-1d600440a7ad

  7. Hello, I am reviewing a data model and I am trying to understand what this calculated column does?

    =
    IF (
    CALCULATE (
    DISTINCTCOUNT ( Recon[TPID] ),
    FILTER (
    ALL ( ‘Recon’ ),
    Recon[CommittedDeals] >= EARLIER ( Recon[CommittedDeals] )
    ),
    FILTER (
    ALL ( ‘Recon’ ),
    Recon[Segment] = EARLIER ( Recon[Segment] )
    ),
    FILTER (
    ALL ( ‘Recon’ ),
    Recon[Region] = EARLIER ( Recon[Region] )
    )
    )
    <= 20,
    "Top 20 Deals",
    "Other"
    )

  8. Rob,

    Looking at the four row table from your “Flashback: Simple Use of the EARLIER Function”, the output of the “Total For Customer” column would appear very similar to doing the following in SQL:

    SUM(Amt) over (PARTITION BY Customer)

    Do you agree?

  9. Hi all, I am having sleepless nights due to one issue. I have a calculated column which basically looks at all the rows for a country with a C flag and sums up the budgets in all those rows and shows it in the row that has both a C and a Y flag. Below is the formula which works OK

    if(AND(Grant[Consolidation line]=”Y”,Grant[Consolidate]=”C”),sumX(filter(All(Grant),[Country ID]=earlier([Country ID]) && Grant[Consolidate]=”C”),[Board approved Grant Budget]),0)

    HOWEVER, whenever i add additional data to the table, i am unable to refresh power pivot and i get an error “unable to update one or more linked table”. When i remove the above formula, however the table refreshes OK.

    Can you please help!!

    Thanks
    Megha

Leave a Comment or Question