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!
David Hager’s guest post below reminded me of something I’ve been meaning to share for awhile. Here’s a feature of PowerPivot so tiny, so sensible, that I’ve had a very hard time internalizing that Excel lacks it. In fact, I think I’ve had to re-check Excel 3-4 times to confirm for myself that Excel doesn’t have it.
The feature is the pair of logical operators && and ||. They are alternatives to the Excel functions AND() and OR(), respectively. Check out these calculated column formulas:
IF(Product[Color]=”Blue” && Product[Weight]>6, “Heavy and Blue”, “Everything Else”)
IF(Product[Color]=”Blue” || Product[Color]=”Red” || Product[Color]=”Yellow”, “Primary Color”, “Blend”)
The first example is looking for Products that are both blue AND weigh over 6 pounds. The second example will tag a product as “Primary Color” if it is Blue, Red, OR Yellow.
Notice how you can also use more than two clauses – neat huh?
Note that using && and || is often a great alternative to the dreaded “nested IF” formula.
Also, I mentioned above that Excel DOES have the functions AND() and OR() that do the same things. But these operators make for much easier formulas to read, and I think they are a lot easier to write as well.
Using || in a CALCULATE measure
I don’t think I’ve had occasion to use && in a CALCULATE measure, primarily because adding another filter clause to calculate achieves the same effect as an AND. But || sure is helpful, here’s an example:
[Return Dollars] = CALCULATE([Sales],
That measure recalculates the [Sales] measure and only includes transactions that were either Returns OR Credits. Neat huh? It’s a great shortcut alternative to adding a calculated column with a nested IF, or even a calc column that uses || – just do it right there in the measure.
And now for another alternative to nested IF’s: David’s post on using a lookup table.