15- IF(), SWITCH(), BLANK(), and Other Conditional Fun

Using IF() in Measures

It is time to introduce conditional/branching logic into our measure formulas. This starts out as simple as you would expect.

Consider our [Pct Sales Growth YOY] measure from last chapter:

[Pct Sales Growth YOY] =

([Total Sales] – [Total Sales DATEADD 1 Year Back]) /
[Total Sales DATEADD 1 Year Back]

Figure 223 We get a #NUM error for 2001

We get an error because [Total Sales DATEADD 1 Year Back] is 0 for 2001 – there were no sales in 2000, so this is really a “div by 0” error.

Technically speaking, [Total Sales DATEADD 1 Year Back] is not returning 0 for 2001, it is returning blank – when there are no rows in the source tables corresponding to the filter context, measures return blank. But when we divide by blank, that’s the same as dividing by zero in terms of causing an error.

This is an easy fix – we just edit the formula, and wrap our original formula in an IF():

[Pct Sales Growth YOY] =

IF([Total Sales DATEADD 1 Year Back]=0, 0,
([Total Sales] – [Total Sales DATEADD 1 Year Back]) /
[Total Sales DATEADD 1 Year Back]
)

And the results:

Figure 224 Now returns 0% instead of an error

The BLANK() Function

We can do better than 0% though can’t we? 0% implies that we had 0 growth, when in reality, this calculation makes no sense at all for 2001.

So rather than return 0, we can return the BLANK() function.

Let’s edit the formula accordingly:

[Pct Sales Growth YOY] =

IF([Total Sales DATEADD 1 Year Back]=0, BLANK(),
([Total Sales] – [Total Sales DATEADD 1 Year Back]) /
[Total Sales DATEADD 1 Year Back]
)

And the results:

Figure 225 Aha! Now 2001 is gone completely, nice!

Why does 2001 disappear from the pivot completely? Because all displayed measures return BLANK() for 2001.

This is a VERY helpful trick. Retuning BLANK() in certain situations will become one of your most relied-upon techniques.

If we add a measure that is not BLANK() for 2001, 2001 is displayed once again:

Figure 226 2001 is displayed as long as any single measure returns a non-blank result

You can force 2001 to display, however, even if all measures are blank. Under Pivot Options, on the Pivot Options tab, are the following two checkboxes:

Figure 227 Check that first checkbox…

Figure 228 …and 2001 will be displayed even when all measures are blank.

The ISBLANK() Function

Excel has this function too, of course, but it’s worth bringing up here. When I test for “=0” as I did in the formulas above, and the measure returns BLANK(), the IF() evaluate to True.

I could have tested for ISBLANK() instead of “=0”, but that would still leave me exposed to an error in the case where [Total Sales DATEADD 1 Year Back] returned a legitimate 0 (meaning, there were rows, but the sum of the SalesAmt column was 0 – rare but possible).

So most of the time, I just test for “=0.” But when you want to distinguish between 0 and BLANK(), ISBLANK() is what you need.

HASONEVALUE()

Another new function in PowerPivot V2. Primarily you can think of this as the “am I in a subtotal or grand total cell?” function, although it definitely comes in handy elsewhere too.

To demonstrate, first let me create the following measure:

[Subcategory pct of Category Sales] =

[Total Sales] /
CALCULATE([Total Sales],
ALL(Products[SubCategory])
)

And here it is with along with [Total Sales], and Category/Subcategory on Rows:

Figure 229 Each Subcategory is calculated as a percentage of its parent Category, in terms of [Total Sales]

Those 100.0% subtotals and grand total are useless though. I’d love to suppress them.

To do this, I am going to detect when my filter context contains more than one Subcategory, because having more than one Subcategory is the definition of a subtotal/grand total cell for that field, as explained in the chapter on ALL().

So I edit my original measure to detect that condition, using the HASONEVALUE() function:

[Subcategory pct of Category Sales] =

IF(HASONEVALUE(Products[SubCategory]),
[Total Sales] /
CALCULATE([Total Sales], ALL(Products[SubCategory]))
, BLANK()
)

HASONEVALUE() is equivalent to IF(COUNTROWS(VALUES())=1 – we used to have to use this latter approach, but now in PowerPivot v2, HASONEVALUE() is much better.

Results:

Figure 230 Subtotals and grand totals suppressed for just this measure, still “on” for [Total Sales]

I could turn off Subtotals and/or Grand Totals via the Pivot Design tab on the ribbon, but that would turn off totals for [Total Sales] as well. I want to do this just for [Subcat pct of Cat Sales].

IF() Based on Row/Column/Filter/Slicer Fields

Our first use of IF() in this chapter tested against the value of a measure. But what if we want to test where we “are” in the pivot in terms of filter context?

For example, what if we want to calculate something a little differently for a specific country?

I’ve added a new lookup table to my model, one named SalesTerritory. It contains a Country column, which I am displaying on Rows, along with my [Sales to Parents] measure:

Figure 231 I don’t trust that number for Canada…

All right, let’s invent a problem. Pretend for a moment that I cannot trust the [NumberOfChildren] column in my Customers table for Canadian customers – something about the way I collect data in Canada makes that number not trustworthy. And that column is the basis for my [Sales to Parents] measure.

So for Canada, and Canada only, I want to substitute a different measure, [Sales to Married Couples], for that measure. (And of course, everyone in my organization is “on board” with this change – I’m not deliberately misleading anyone!)

So, how do I detect when Country=Canada? I’ll give you the measure formula first and then explain it.

[Sales to Parents Adj for Canada]=

IF(HASONEVALUE(SalesTerritory[Country]),
IF(VALUES(SalesTerritory[Country])="Canada",
[Sales to Married Couples],
[Sales to Parents]
)
,
BLANK()
)

The VALUES() Function

First, let’s explain what this VALUES() function is all about. Quite simply, it returns the filter context as specified by the pivot. So sometimes it returns a single value for a column, and other times it returns multiple values (if you are in a total cell).

Examples:

Figure 232 For the highlighted measure cell, VALUES(SalesTerritory[Country]) returns “Canada”

Figure 233 In this case though, it returns multiple values: {“Australia”,” Canada”, “France”… , “United States”}

OK, now let’s work from the inside out and explain the formula.

1. IF(VALUES(SalesTerritory[Country])=”Canada” – we cannot directly test IF(SalesTerritory[Country]) – that violates the “no naked columns” rule of measures. And since Country is a text string, we need to use something other than MIN, MAX, etc., so we use VALUES().

2. IF(HASONEVALUE(SalesTerritory[Country]) – If we perform an IF(VALUES()) =”Canada” test in a case where there is more than one value, we will get an error. So we need to “protect” our IF(VALUES()) test with the IF(HASONEVALUE()) test, and only let the IF(VALUES()) test “run” in cases where there is only one value.

OK, let’s see the measure in action:

Figure 234 Our special measure differs only for Canada, as desired.

Using VALUES() for Columns That Are Not on the Pivot

You are not restricted to using VALUES() with columns that are on the pivot. In fact it is often quite useful to use VALUES() with a column that is not used.

For instance, let’s look at this pivot that has two fields from the Products table on Rows (Category and Color), and the simple [Product Count] measure:

Figure 235 Simple Products pivot

Now let’s focus on a single cell:

Figure 236 For the highlighted measure cell, what does VALUES(Products[Color]) return?

Figure 237

In this case, VALUES(Products[Color]) returns {“Black”, “Blue”, “Red”, “Silver”, “Yellow”}.

Note how “Grey” and “NA” are not returned for this “Bikes” measure cell, but those two colors are returned for Accessories. This is because Category and Color (the fields on Rows) are both columns from the Products table, which means that a Category filter has an impact on what is valid for Color. Category=”Bikes” filters the Products table, and there are no Bikes of Color “Grey” or “NA”.

The same sort of thing would be true if Color came from Products and Category came from a related table, one that had a Lookup Table role with respect to Products (since Lookup tables filter their partner Data tables).

Now, if I remove Color from the pivot, what does VALUES(Products[Color]) return?

Figure 238 Same pivot cell after Color has been removed – what does VALUES(Products[Color]) return?

It returns exactly the same list as before: {“Black”, “Blue”, “Red”, “Silver”, “Yellow”}.

Whether Color was on the pivot or not, the cell we had highlighted did not have any direct filters applied for Color. The only Color filters were those implied by the Category filter context, which is still there.

So if we had Calendar[Year] on the pivot in place of Products[Category], VALUES(Products[Color]) would return all colors, since Calendar and Products have no relationship between them.

VALUES() Only Returns Unique Values

I had the [Product Count] measure on the pivot for a reason:

Figure 239 There are 35 Products in the Accessories Category, which is 35 rows of the Products table, but only 6 different values for Color.

So even though the filter context has 35 rows of the Products table “active” for the highlighted cell, COUNTROWS(VALUES(Products[Color])) would return 6.

To drive that home, let’s do exactly that:

[Color Values] =

COUNTROWS(VALUES(Products[Color]))

Figure 240 Proof that VALUES() only returns the unique values

SWITCH()

What if we want to do something different for multiple different countries though, and not just Canada? Nested IF()’s are one way of course, but the new function SWITCH() is much cleaner.

Here’s a completely arbitrary example, since it’s hard to come up with something realistic using sample data:

[Different Number per Country] =

IF(HASONEVALUE(SalesTerritory[Country]),
SWITCH(VALUES(SalesTerritory[Country]),
"Australia", 6,
"Canada", 12,
"France", 18,
"Germany", 24,
100
),
BLANK()
)

Notes:

1. Starting with the second argument, SWITCH()’s arguments operate in pairs – if it matches “Australia” it returns 6, if it matches “Canada” it returns 12

2. If you end SWITCH() with an “odd” argument, that is treated as the “ELSE” – the 100 is by itself, not paired with another argument. So if the current value doesn’t match any of the prior tests, 100 will be returned.

3. SWITCH() still needs the “protection” of IF(HASONEVALUE()) if you are using a VALUES() as the first argument to SWITCH – if I were using an arithmetic function like AVERAGE(), it would not be necessary (just as it’s not necessary with IF). Really, you should think of SWITCH() as a multi-branch version of IF().

And the results:

Figure 241 Results of the SWITCH() measure