DAX – The Friendly Neighborhood Operators && and ||

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.