The Friendly Neighborhood Operators && and ||

April 22, 2011

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.


Guest Post: Calculating a Sum Based on a List Criteria

April 22, 2011

Guest Post By David Hager

It is a fairly easy task in PowerPivot to calculate a sum based on N criteria (after a learning curve). This can be accomplished by filtering your data before importing it in the PowerPivot window, selections made in the Pivot Table, or through some (relatively) simple DAX calculations.

However, if N gets too big, the task get much bigger. Now, imagine a table with thousands of customers and the task is to calculate a sum based on only 1000 of those customers. Conventional filtering does not provide a way to do this. Then, say that this list of customers changes from day to day. It would be nice to have a method to perform this calculation. There is!

The first step is to create a linked table from your Excel workbook to its PowerPivot window. The table in PowerPivot is named LookupList. Then, a relationship is created between that table and an existing Table1 (as shown below).

image

The list shown above does not have 1000 entries, and is just for demonstration purposes. However, I have tested it with >1500 entries and it works :)

Solution One:  Calc Column as Basis for Measure

One solution is to create a calculated column named AggregateList that returns only the amounts that are associated with the customers in the lookup table. The correct formula to do this is:

=IF(ISBLANK(RELATED(LookupList[Customer])),BLANK(),Table1[AMOUNT])

Now, a pivot table can generate the desired result as shown below.

image

By changing the aggregation of the measure, an average or other value can also be obtained.

Solution Two:  Yes/No Column plus CALCULATE Measure

(From Rob):  Rather than produce the numerical column as David did above, my first instinct was to use the LookupTable to generate a simple yes/no column, and then use that as the filter in a CALCULATE measure.

Calc column:

=IF(RELATED(LookupList[Customer])<>"",1,0)

image

and then the measure:

=CALCULATE(SUM(Table1[AMOUNT]),Table1[IncludeCustomer]=1)

This is mostly a matter of style.  Both require a calc column – this option’s calc column is simpler but has a more complex measure.  (Neither approach is all that complicated however.)  This option might make for a smaller file size, but it’s hard to be certain of that, and even if true, it won’t be much.

Back to David.

Refreshing the customer list

I mentioned at the beginning of this article that the customer list provided by the linked table is dynamic by nature. If the list is changed manually, recalculation will occur if the PowerPivot window is opened. However, if the list is generated from Excel formulas some calculation errors can occur. Instead, the use of other methods to update this list is preferable. The details of these issues will be discussed in a future article.