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!
“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]=
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:
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.