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!
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:
…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:
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:
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
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:
ISFILTERED() is Not “Tricked” by the Nefarious Region 0
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