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],
Sales[TransactionType]=”Return” ||
Sales[TransactionType]=”Credit”)
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.
