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

A Common PowerPivot Trick:  Having a Measure Do Something Different on SubTotal and GrandTotal Cells

A Common Trick:  Having a Measure Do Something Different on SubTotal and GrandTotal Cells

A Progression of Technique

It’s been awhile since I’ve written a good old honest formulas and techniques post, so I figured today would be a good time to “get my hands dirty” again.

Ever since PowerPivot v1, I’ve been writing measure formulas that “detect” whether the current pivot cell is a “total” cell (either a subtotal or grandtotal), and then doing something different if it is.

In the image above, my [Blank if Total] measure returns BLANK() for all totals, otherwise it returns the same value as the original [Units Sold] measure.

In PowerPivot v1, that formula would have looked like:

    [Units Sold],

…which basically says “if I have more than one value for StoreName, return BLANK(), but in cases where I have only one value, return the original measure.”

That was clumsy – a lot to write for a simple “do I have one value” test.  So in PowerPivot v2, they gave us the HASONEVALUE() function which makes things cleaner:

    [Units Sold],

Useful in Many Situations

Returning BLANK() for totals is just one application of the technique above.  Here’s an old post where I use that technique, as well as a “branching measure” formula.

I also use it in cases like making totals add up correctly, which is one of the most popular posts on this site on an ongoing basis.

A Minor Flaw Though

“Having one value” and “not being a total cell” are not precisely the same thing.  For example:

HASONEVALUE Gets Fooled by Some Cells

Region 0 Only Has One Store, So it “Tricks” Our Formula!

Get it?  Region 0 only has one store – store 26.  So, in the context of the Region 0 total cell, there’s still only one store!  Our formula is fooled!

Sometimes, that won’t matter.  But when it does matter, we need an alternate approach.

ISFILTERED() – An Alternative

    [Units Sold],

OK, ISFILTERED() is different.  It explicitly checks to see if we’ve got a filter on StoreName in our current cell.  And this fixes the flaw from above:

PowerPivot also Gives us ISFILTERED() Which Doesn't Get Fooled

ISFILTERED() is Not “Tricked” by the Nefarious Region 0 Smile

This is all either quite clear or quite confusing, depending on your mindset.  Totals are really just the absence of filters – in the pivot above, all of the total cells have no filter on the [StoreName] field.

Most of the time, when you have an absence of a filter, you are going to have more than one value for that unfiltered field.  Sometimes you don’t.  ISFILTERED skips all of that and just asks “no really, is [StoreName] filtered right now?”

(The concept of total cells being the absence of filters is covered in pages 65-67 of the book, for those of you who want to reference it).

OK, it’s good to know we have a way to fix this.  But there’s another function lingering out there – HASONEFILTER(), and my spider sense is tingling a bit that there’s more to cover here. 

So I’ll loop back to this on Thursday 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 5 Comments
  1. I don’t want to be a wet blanket but, according to my tests, if you remove “Store 27” from the selection, it will change the result for all regions. HASONEFILTER() seems to do a better job.

    1. Yep. And if you put StoreName on a slicer and performed a multi-select, you’ll get similarly “weird” results. You are jumping ahead to Thursday’s post… which is of course 100% expected 🙂

      1. Not to mention that if you want to collapse the entire store level to show regions only, you end up with an empty table, or if you collapse the stores in any region, the region disappears from the table. I have no doubt though, that your Thursday post will address these issues.

  2. Rob – I have this DAX problem can you help? I am trying to summarize data (by Year) as shown below (line 1) under Expected results. I need to add up both contractedQty(fixed) and Actual Qty. How can I achieve this using DAX?

    DATA –
    contractnumber Year-Period material [QtyContracted For full Year] [ActaulQtyForPeriod]
    1234 2015-01 a 100 50
    1234 2015-04 a 100 40
    1235 2015-05 b 25 2
    4578 2015-01 a 600 200


    contractnumber Year material [QtyContracted For full Year] [ActaulQtyForPeriod]
    1234 2015 a 100 90
    1235 2015 b 25 2
    4578 2015 a 600 200

  3. hello, I am new to DAX, but facile with SQL. I tried using your example, but my pivot table ignores the ‘IF’ expression. I have tried all three suggested approaches, but I always get BLANK. Do you have any suggestions of what I might be doing incorrectly? Excel 2016, MS Analysis Services Tabular cube.

Leave a Comment or Question