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],
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.

#### Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

1. Bob Phillips says:

<>

Uh? What does && and || do that AND and OR doesn’t do, perhaps you would you care to elaborate how this is an alternative?

The Excel table formula

IF(OR(Product[Color]=”Blue”, Product[Color]=”Red”, Product[Color]=”Yellow”), “Primary Color”, “Blend”)

looks the same to me.

1. Hi Bob. Yes, I care to elaborate.

These operators result in much more readable formulas, and are simpler to construct. Psychologically speaking, it’s a lot easier for me to put the “and” where it occurs in the sentence – between the filter clauses, than it is to go back and put it before the filter clauses, wrap the clauses in parenthesis, etc.

It’s the same reason why I prefer the shorthand version of CALCULATE:

[Measure](filter1, filter2, …) is so much easier to read and write than:

CALCULATE([Measure], filter1, filter2…)

But hey, that’s just my preference. You clearly love AND and OR 🙂

I’ve clarified the post with an additional paragraph as well, because you probably won’t be the only one with this question. Thanks Bob.

2. Bob Phillips says:

That should have been quoting the line … Note that using && and || is often a great alternative to the dreaded “nested IF” formula. I always forget that < is markup.

3. Bob Phillips says:

Well it read to me that you were saying that you had to have nested IFs in Excel if you want multiple tests, but if you were just saying that (cond OR cond OR cond) is easier to read then, hey, Excel has that too

=IF((ProductColour=”Blue”)+(ProductColour=”Red”)+(ProductColour=”Yellow”), “Primary Colour”,”Not”)

you want ANDs,

=IF((ProductColour=”Blue”)*(ProductWeight>6),”Heavy Blue”,”Not”)

1. Maybe the post was updated, but I didn’t read it that you had to do nested IFs in Excel. The idea was to simplify the expressions and improve readability in existing expressions with PowerPivot (eliminating nested operations).

The other nice thing about these operators, && and ||, is that they provide a short-circuit evaluation.

2. Excellent point Bob. I always forget that + and * can be used that way in Excel, and for whatever reason, I’ve never adopted them. I’ve always just found them unnatural in some way, I still much prefer && and ||, but this is excellent clarification in case others with your level of Excel skill are confused by the post.

4. Bob Phillips says:

… in case others with your level of Excel skill are confused by the post

Ouch!

1. Jack says:

Using and + or in dax limits u to 2 arguments. && + || unlimits arguments quant.

5. Mannu says:

Hi,
Is there a way I can implement Bitwise AND and BITWISE OR operation using DAX?

For example I need to find (SUM[Field1] & SUM[FIELD2])

Thanks,
Mannu