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**.