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

Snatch - A Damn Fine Movie


“However…  you do have ALL() the characteristics of a dog, Gary.  ALLEXCEPT() loyalty…”




This will be a quick one.  I think.

In part two, we left off with the observation that VALUES() can “trump” ALL() even when VALUES() is applied to a column that is not on the pivot:

[ALL then VALUES of field not on pivot] =
[Total Sales](ALL(Periods),VALUES(Periods[Period Num]))


One thing I have often puzzled over is this:  how does a combination of ALL(Table), VALUES(Table1[ColumnA]) compare to using ALLEXCEPT(Table1, Table1[ColumnA])?

In essence, those should do the same things right?  In one case, you set the whole table to ALL(), but then “restore” ColumnA to its pivot context using VALUES().  In the other case, you set every field in the table except ColumnA to ALL(), leaving ColumnA in the context set by the pivot.

They should result in the same thing, right?  Let’s check.

ALLEXCEPT() using columns that are on the pivot

[ALLEXCEPT fields on Pivot]=
[Total Sales](ALLEXCEPT(Periods,Periods[Year],

There are two fields on the pivot from the Periods table – Year and FiscalQuarter, and both of those are excluded from being “all’d” in the measure.  Results:


OK, good news.  Those are indeed the same results as the unfiltered base measure, and that’s what we got from ALL() plus VALUES() as well.  So from this, we can say that ALL() plus VALUES() is the same as ALLEXCEPT().

Not so fast though…

ALLEXCEPT() using columns that are NOT on the pivot

Let’s try ALLEXCEPT() using a field that is NOT on the pivot.  Remember, VALUES() trumped ALL() in this case, too, and the results were the same as the unfiltered base measure.  Will ALLEXCEPT follow suit?

[ALLEXCEPT field NOT on Pivot]=
[Total Sales](ALLEXCEPT(Periods,Periods[Period Num]))


OK, that was a bit unexpected.  ALLEXCEPT() returned the same results as pure ALL().  So ALLEXCEPT() does *not* preserve the pivot context of columns NOT on the pivot, whereas VALUES() *does* pick those up.

I’m not sure if that’s intentional on the part of the PowerPivot team, or if it’s just a quirk.


1) ALLEXCEPT() does behave precisely like ALL(), VALUES() as long as the “exempted” columns are columns on the pivot.

2) ALLEXCEPT() does NOT preserve pivot context, however, on columns that are not on the pivot.

Oh, and one more…

No need to use ALLEXCEPT() instead of ALL() when using VALUES()

In the past, whenever I wanted to do an ALL() with a VALUES(), I typically used an ALLEXCEPT() instead of the ALL().

For instance, I would do something like this:

[Total Sales](ALLEXCEPT(Periods,Periods[ColumnA]),

That turns out to have been an overly-careful habit.  As we’ve seen here, and in part two, VALUES() trumps the ALL(), and does not need the “help” of ALLEXCEPT() instead.

Yes, I’m sure many of you will say that’s obvious from everything covered so far, but just in case you find yourself tempted to use ALLEXCEPT() to exempt the column you are preserving with VALUES(), there’s no need.

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 3 Comments
  1. I have created a measure in Power Pivot and I am able to calculate subtotal from it and repeat the values in another column as shown below. The issue is that it doesnt take into account the external filters on the pivot table.

    Client | Type | SubType | DT(1/2) | DT(1/3) | DT(1/4)
    | | |CurrDay | PrevDayST | CurrDay | PrevDayST | CurrDay | PrevDayST
    ABC X AA 100 0 101 600 1000 306
    ABC X BB 200 0 102 600 2000 306
    ABC X CC 300 0 103 600 3000 306
    ZZZ Y AA 450 0 100 1500 4000 505
    ZZZ Y BB 550 0 205 1500 2000 505
    ZZZ Y CC 500 0 200 1500 3000 505

    Here are the steps –

    1) I created a measure to calculate Previous Day Amount –
    *Table1 contains all the data

    *Table2 is the Date table

    *Table1 and Table2 are linked

    PREV-DT-AMT= CALCULATE(SUM(Table1[NET-AMOUNT]), FILTER(all(Table2[DT]), Table2[DT] < max(Table2[DT]) ))
    2) In this step I calculate subtotals shown in above table under column "PrevDayST"


    The issue here is that when I use filters on the pivot table to filter out some Sub-Types the subtotal measure in step2 doesnt take into account the filters in the pivot table. I understand that allexcept ignores all the external filters. I would highly appreciate if you could give me a solution that will let me calculate subtotal from measure [PREV-DT-AMT] and repeat it under "PrevDayST" WITHOUT ignoring external filters.

    I have been struggling with this for the past couple of days. Thanks in advance.

  2. It would be nice if you stayed consitent. In parts 1 & 2 you explicitly identified 4 “Rules”. Now in part 3 you stopped doing that. So, did you list 1,2, or 3 new rules?

Leave a Reply

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