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

Richard Pryor wrote most of Blazing Saddles, explaining why it's the best movie Mel Brroks ever made

“Wait a minute. There might be legal precedent. Of course. Land snatching. Let’s see, land, la-land…see snatch. snatch…snatch…ah! Haley vs. United states. Haley 7, United States nothing. You see, it can be done!”



Keeping filters straight as they pile up

Some example questions to ponder:

  1. Within a FILTER or a CALCULATE, does the order of the filter arguments ever make a difference?
  2. Does using an ALL inside of a FILTER yield different results than using an ALL inside of a CALCULATE?
  3. Measures that are used inside of a FILTER or DATESBETWEEN are never impacted by external filter-setting forces are they?  Or are they?

I went through several months without learning anything new about DAX, and that seemed to me like a Good Sign.  A sign that perhaps I now knew everything I needed to know – not everything I could know, but everything I would ever really need to know.

But then a series of new scenarios presented by clients opened up my eyes to some very “squishy” places in my understanding.  I got everything working for the clients, but I took a note to dig in later and see if I could capture everything into a series of new “rules.”

In the meantime I fired off a few emails – specifically to the product team and “The Italians” – and got some initial advice (the Italians were particularly helpful).  But both of those parties understand this stuff almost TOO well – and what Excel people like me need, I think, is to learn from someone who struggled initially.

So I propose to be that person – who struggles with questions like the above, then triumphs, then explains.  I am going to post my findings as I go, but rest assured that it’s not an entirely selfless act – forcing myself to explain things is the way I best learn things myself Smile

It’s gonna take a series of posts to cover this.  And frankly, I’m not done discovering all the rules yet.  Let’s dig in.

Setting the stage:  two simple measures

We need a base to start from.  So, here’s a simple pivot:

Starting Point for PowerPivot Filter Precedence

The measure there is nothing fancy – just a simple SUM.  All of the fields on rows are from the Periods table:

Periods Table

Now let’s add an ALL(Periods) version of the measure:

[ALL Period Sales] = [Total Sales](ALL(Periods))

Starting Point for PowerPivot Filter Precedence with ALL

And if the syntax I am using, [Measure](<filters>), makes you scratch your head, remember that it is just shorthand syntax for CALCULATE([Measure], <filters>) – they are 100% the same, I just prefer the shorthand syntax for readability.  (This is a controversial topic within the DAX community however, and others disagree with me, but I’m right, damnit! Smile)

Rule #1:  “ALL()” and “Table[Col]=Val” Work Together

Let’s add a third measure, one that has the ALL() from above, but also sets Year=2009:

[ALL Period Sales Set to Year 2009] =

[Total Sales](ALL(Periods),Periods[Year]=2009)

And the results:

ALL and Simple Filters Work Together

Neither clause “overruled” the other.  They were both applied.  We can think of this as “all Periods filters were completely removed, but then Year was set to 2009.”

Of course, thinking of it that way makes you wonder…  what if I reverse the clauses and have the ALL second?  Will that then override the simple filter for 2009?  I mean, I hope that doesn’t matter.  Marco and Alberto say it doesn’t matter.  But I need to see it myself…

Rule #2:  Filter order does NOT make a difference within a CALCULATE

OK, let’s reverse the order:

[ALL Period Sales Set to Year 2009] =

[Total Sales](Periods[Year]=2009, ALL(Periods))

And yes, same result:

Order does NOT matter in filter clauses

OK, that’s reassuring.  But it gets more interesting from here.  In the next post, I’m going to show you that when ALL() meets VALUES(), there arguably IS a winner, and that ALLEXCEPT() and VALUES() yield outcomes that I did not expect.

Rob Collie

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 7 Comments
  1. One point to note here is that in this example explicitly applying ALL(Periods) is completely redundant. If you just apply the filter Periods[Year]=2009 then that implicitly applies ALL(Periods); otherwise the measure would be blank for all rows not in 2009. Intuitively I would expect to need the ALL(Periods) if you’d want the measure to show up for other years but I imagine the way they programmed the function is more in-line with what users would actually want to see.

    1. That is not true. When I remove the ALL() from that measure it definitely changes the results. The measure that just sets Year=2009 does NOT affect other columns in the Periods table, so the resulting measure still respects Qtr, etc. For the 2009 region of the pivot, the resulting measure is identical to the original Total Sales measure. And then for subsequent years, it repeats the 2009 values.

  2. “shorthand syntax for CALCULATE([Measure], ) – they are 100% the same”… I have a situation where my measure, [BalanceEq], might sometimes yield a negative numerical result, and after testing your theory, found that there is in fact at least one difference between the results of the two methods:



    The shorthand version just zeroed (blanked, really) any negative numbers, whereas the longer version retained them.
    I don't pretend to understand why this is the case, I just thought I'd share my observation.

Leave a Reply

Your email address will not be published. Required fields are marked *