How to use the SWITCH() function in Excel Power Pivot

It’s amazing to think that everything we do on our computers (at least for now) is powered by a series of True/False states. And while most of these operations are done behind the scenes, there’s still a fair amount of conditional or Boolean logic found in many data analysis tasks.

If/then Excel statements start simple enough. But add a few layers and they quickly become a nightmare to decipher:

Many programming languages like SQL or Tableau provide CASE statements to write cleaner conditional logic statements. DAX in Excel Power Pivot provides something similar with the SWITCH() function. Let’s look at some examples.

Creating SWITCH() as a calculated column

In general, SWITCH() is used to recode values using a set of conditional statements. Because each row is evaluated individually and these results are not likely to be aggregated, it makes most sense to store SWITCH() results as a calculated column rather than a measure.

A simple SWITCH() to recode values

At its most basic, the SWITCH() function can be used to recode values. For example, we might want to number the segments Consumer, Corporate and Home Office as 1, 2 and 3 respectively. If a match is not found, we’ll recode the value as "Unknown":

orders_recode: = SWITCH(
    [segment],
    "Consumer", "1",
    "Corporate", "2",
    "Home Office", "3",
    "Unknown"
  )

Remember, every value in a data model table must be of the same type, so because we are including an "Unknown" as a string we must also make the other values like 1 and 2 strings.

Using SWITCH() with TRUE() to bin quantities

In the previous example we recoded the segment variable so that Consumer became segment 1, Corporate became segment 2 and so forth. But what if we wanted to create grouped or binned variables, such as the following:

If the order quantity is less than 3, call it a “Small order.” If it’s less than 6, call it a “Medium order.” Otherwise, call it a “Large order.”

This case is a little more complex, as we are evaluating a series of logical tests rather than simply matching specific values. Because we want every conditional statement to be evaluated at once and the best result returned, we will start the statement with TRUE() to force a result:

= SWITCH(
    TRUE(),
    [quantity] < 3, "Small order",
    [quantity] < 6, "Medium order",
    "Large order"
)

With these columns derived, go ahead and load your data model to a PivotTable. From here, you’re able to slice and count records by these recoded variables. For example, here’s a count of order sizes by segment:

If SWITCH() is helpful, then use it!

SWITCH() is great for a number of use cases. For example, you might want to group two categories into one, recode uncommon values as "Other" and more. Remember, however, that as more columns get created, the memory needed to store your data model will expand. So as always, compute with caution.

How have you used SWITCH() before in Power Pivot? How does it compare to the classic Excel IF() operators? Let me know in the comments.