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

Post by Rob Collie

**In the Classic Physics Thought Experiment, Schrodinger Hypothesized a Cat That Was Simultaneously Dead AND Alive.
**

*(But here we will use the more humane “Simultaneously Green AND Grey.”*

### Back to Basics!

**PowerPivotPro.com celebrated its 5th birthday back in November.** Over 5+ years, we’ve progressively covered techniques with an increasing level of sophistication. That’s pretty natural – we ourselves have become more skilled over time, AND there’s a tendency to not want to write the same post twice.

**But today I want to drive home a basic point,** one that will help “recruit” Excel pros into the Power BI camp, AND that will help “crystallize” a few things even for the longtime DAX practitioners.

### Schrodinger’s Cat – A Classic Battle of the Nerds

**In 1935, physicist Erwin Schrodinger wanted to show Albert Einstein how wrong he was.** Einstein had recently published a paper that made an astounding claim about the nature of subatomic particles. If those claims were true, said Schrodinger, even “big” everyday stuff, like cats, could also behave in that same outlandish way. Which made Einstein look kinda silly, in Erwin’s mind.

**He proposed the idea of a cat that was both simultaneously alive AND dead,** and basically said “See, Albert? Alive AND dead is clearly impossible, so your theory is junk.” See **here** for details.

**But modern quantum physicists actually think the cat experiment does NOT disprove Einstein’s claim.** In fact, they think Schrodinger’s Cat demonstrates that the universe is fundamentally a MUCH stranger place than we typically think.

**In short, the concept of “impossible”** is subject to re-evaluation.

### This PivotTable is Simultaneously Filtered AND Unfiltered

**A Simple Little Picture Yes? But it Hints at a Quantum Shift in Analytical Power.**

**“The pivot pictured above is impossible,” says Schrodinger.** Sorry, you just can’t do it. Nope, no way. The “Just Bike Sales” column is behaving as if the pivot is filtered to Bikes and the “Total Sales” column is behaving as if there’s no filter at all.

**You CAN do that of course, but you can’t do it at the same time.** You have to add a report filter (or a slicer) and then keep toggling it back and forth:

**Just Keep Toggling the Pivot’s Report Filter Back and Forth
**

*Sometimes this is fine, sometimes it’s clumsy.*

### “Simultaneous” IS Possible with Two Pivots

**Excel users are a clever sort,** and sooner or later you realize we can just have two pivots:

**Have You Ever Done This – Two Pivots of the Same Shape, But With Different Filters Applied?
**

*I Sure Have.*

**And then, the next logical step: in-grid formulas** to calculate the ratio or difference between the pivots!

**You May Have Done This, Too.
**

*(In this case I eschewed GETPIVOTDATA and used A1 ref, but have done it both ways – a lot).*

### But Then, Humanity Throws a Wrench Into the Works

**You’re all done.** You’ve built your multiple pivots, written your formulas, and present your results.

**The first thing they say is **“awesome, but we also need to see it broken out by X.”

**You want to strangle “them.”** Don’t do it, but I understand why.

**Because when you add another field to the pivot, your in-grid formulas** need to be re-written, and sometimes you need to completely re-organize your worksheet because the pivots, as they change size, start colliding with one another.

**So you tell them no, not possible in the time allowed. ** Which is often the absolute truth – a simple change like that quite frequently is a “start from scratch” situation.

### Be Like Einstein. Use CALCULATE.

**This IS Possible, With Power Pivot and/or Power BI.**

**Just a simple little formula:**

[Just Bike Sales]:=

CALCULATE([Total Sales], Products[Category]=”Bikes”)

**One pivot, simultaneously behaving as if it’s filtered AND unfiltered** – one column is, one column isn’t.

**This is NOT possible with normal pivots.** OK sure, you can add a calculated column in the original source data that contains 0 unless a sale is a bike sale, and the full amount if it is, and then you put THAT column on your pivot. But that takes forever, and litters your source data with dozens of columns over time. You know you CAN do it, but you DON’T do it – at least not very often.

**But then we pile on, **add another formula, and we’re off to the races:

[Pct of Sales from Bikes]:=

[Just Bike Sales] / [Total Sales]

**Our Formula for Percentage is Now in the Pivot!**

### We Can Rearrange the Pivot and the Formula “Fills Down” Automatically – Grows/Shrinks/Adjusts with the Pivot!

**Added Weekend/Weekday on Rows, All Good**

### We Can Even Take the Original Values OFF the Pivot, And JUST See Pct!

**Our Formula Keeps Working Even though Its Inputs are No Longer Displayed ANYWHERE**

**Rearrange? Drag Country to Columns? No Problem.**

### Schrodinger’s Head Explodes

**Take a look at those last two pictures.** In those pivots, EACH CELL is behaving simultaneously as filtered and unfiltered. It’s dividing the filtered amount by the unfiltered amount.

**And honestly, this is one of the more MINOR** examples of CALCULATE’s power. We could be here for a long time if I tried to show an exhaustive list.

**Since 2009 we’ve been saying CALCULATE is a supercharged SUMIF.** And Einstein would approve.

Rob, what a great way to frame the power of CALCULATE() ! Simultaneously filtered and unfiltered. Supercharged SUMIF(). I think that is what drew us to your content initially and inspired us to step out and begin helping others. You continue to inspire Rob. Keep it up.

When you add the CALCULATE feature with the ability to generate calculations from multiple tables (like actual and budget) with different granularities, and then FILTER() to change the context… well, it is kind of mind-blowing.

Keep them coming Rob!

Another nice post. Reminds me of one of my family’s sayings – “It’s the same, but different.”

Rob,

Here is something strange about Filter / Calculate

1. Define mSales:=CALCULATE(DATA[SALES])

2. Define mPrevYear:=MIN(CAL[CAL_YEAR])-1

3. Define

mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))

Gives us Blanks

But

4. Define

mSalesPrevYear:=

CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=MIN(CAL[CAL_YEAR])-1)) works as expected

So Define once – use every where does not seem to work in all cases !

Also in the above we could do previous year sales simply by using

=CALCULATE([mSales],PREVIOUSYEAR(CAL[CAL_DT]))

But it is still strange the way FILTER behaves the way it does

Yes this is by far the #1 most arbitrary thing about DAX. The one time I think the DAX team perhaps made a mistake – which is an amazing thing really, to only have one “ouch” mistake in the whole language.

CALCULATE() promotes row context into filter context. In a lookup table, try writing a calc column that sums up a column in a related data table. If you use =SUM(), you get the grand total of the entire data table. But if you do CALCULATE(SUM(…)), suddenly you get the sum of the related rows.

So far so good, I’m ok with that.

The real weird thing is that when you use a measure name, that silently IMPLIES a CALCULATE.

So the row context becomes a filter context again.

And within FILTER, each row that’s examined becomes its own row context.

When we convert that row context into a filter context, we then look at a row and compare it TO ITSELF, rather than to all rows in the CAL table. And no row is equal to itself minus one. So we get blanks.

But when we use just the “naked” MIN, we compare the currently-being-examined row to the MIN of all rows from the PIVOT’s filter context, which is what we wanted.

@Rob – My head is spinning reading your explanation 🙂 – It would be really great if you could do a full blog post with “Green” Diagrams explaining this whole thing about the row context becoming a filter context again ! 🙂

Now to go and read it again to see if I can understand it the second time around

@Rob,

I did some more “thinking” – It just got me confused further

Lets assume you want to create a measure that shows growth since inception and you don’t want to hard code the first year

1. Create a measure mFirstYear:= YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))

2. mSalesFirstYear:=CALCULATE([mSales],FILTER(ALL(CAL),CAL[CAL_YEAR]=[mFirstYear]))

Works Fine !!!!

3. mSalesGrowthInception:=DIVIDE([mSales]-[mSalesFirstYear],[mSalesFirstYear])

3 mSalesFirstYear:=

CALCULATE([mSales],FILTER(ALL(CAL),CAL[CAL_YEAR]=YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))))

Also works fine

Now I am comparing

mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))

Where I put a measure in Filter Criteria of Filter and it messed up Calculates Filter context

But it didn’t mess it up in mSalesFirstYear

Now this would have confused Mr Schrodinger and Einstein and the Cats 🙂

I definitely need a “green diagram” post to understand whats Calculate up to…

mPrevYear:=MIN(CAL[CAL_YEAR])-1

mFirstYear:= YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))

As Rob stated, when you use a measure name, that silently IMPLIES a CALCULATE.

CALCULATE always transforms row context into filter context.

However, due to the ALL-function, the second measure does not care about the additional filter context.

Thus, there is no difference between

mSalesFirstYear:=CALCULATE([mSales],FILTER(ALL(CAL),CAL[CAL_YEAR]=[mFirstYear]))

and

mSalesFirstYear:=CALCULATE([mSales],FILTER(ALL(CAL),CAL[CAL_YEAR]=YEAR(FIRSTDATE(ALL(CAL[CAL_DT])))))

while

mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=[mPrevYear]))

and

mSalesPrevYear:=CALCULATE([mSales], FILTER(ALL(CAL),CAL([CAL_YEAR])=MIN(CAL[CAL_YEAR])-1))

differ.

Sam, Rob,

I was just going to write you Rob to tell you I could draft up a guest post about a pattern I use that is a good example of schoedinger filtered/unfiltered behaviour – it is quite similar to what Sam is using – so I have a calculated column in a table specified as:

CALCULATE([Share_of_X_in_Y_for_Year_Z],

FILTER(DimYears, DimYears[Year]=RELATED(DimYears[SourceYear])))

The table, obviously, has a year column which is related to Dim Years (also X is related to DimX and Y to DimY). What I am doing is I have some source data on imports of commodity X by sector Y, but not for all years. So I need to use the shares from some of my source years in some of my years with the data missing. At first I was using an IF formula, e.g.

IF([Year]=”2008″, CALCULATE([Share_of_X_in_Y_for_Year_Z],

FILTER(DimYears, DimYears[Year]=”2009″), [Share_of_X_in_Y_for_Year_Z])

But then I needed to do it for more than one case, and I wanted the ability to try using different base years for the years with the missing data, so I added a column to my DimYears table that would allow me to specify for a given year which source year to use, e.g.

Year SourceYear

2008 2009

2009 2009

…

Then I used the formula above… and I was frankly quite surprised that it worked! It really quite boggles the mind… in order to find the related year, it has to know the current year for the row and relationship that has with DimDate, but then the filter simultaneously tells the function to ignore that relationship completely and manufacture a new relationship with DimDate

.. some brackets out of place in there… but I think you can figure that out.

How would you get combined Total Sales for both “bikes” + another criteria? lets say, “skateboards”

Hi Liza, try the || operator:

https://powerpivotpro.com/2011/04/the-friendly-neighborhood-operators-and/