Sign in or start a free trial to avail of this feature.
8. The Switch Function
Testing many conditions using IF statements can easily become difficult to read. The switch function provides an alternative that is much easier to understand.
- When testing multiple conditions, a switch statement can be easier to write and understand than several nested if statements
- We specify a column to check, followed by pairs of products, and their classifications
- Providing a default value means we do not need to write out every product from the final category
- F4 – Enter formula bar
- Shift + Enter – Create new line (when creating formula)
- Control + C Copy
- Control + V Paste
In previous lessons, we've seen how to use nested if statements to divide data into several categories, however too many nested if statements can make our formulas difficult to read. In some cases, you may be able to solve this problem using the switch function.
In this lesson, we'll use the switch function to divide the products in out data set into three price ranges; cheap, mid range, and expensive. As the price ranges are fixed attributes of the product, we'll add them as a new column in the product details table. In the Power Pivot window, we'll double click, add column, and call it, price range.
We'll start the formula by entering the function, switch.
The switch function checks a single column for a list of values we define, and assigns a corresponding result for each value.
In this case, we'll check the product name against a list of products whose price range we know to create the column. This may sound a bit confusing, but the switch function is very intuitive when you see it. The first argument is the column we want to search, which is the product name column.
Next, we'll create a paired list products, and their price profiles.
There are three products, which we know to be expensive; Melacaine, Sycalink, and Zenatene.
Therefore, we'll type Melacaine, and expensive, then, Sycalink, and expensive, and Zenatene, and expensive.
Notice that I created a new line for each pair. This is not strictly necessary, but it makes it much easier to understand the function.
We'll now add the three mid range products; Lumesta, Novatane, and Carmalume, in exactly the same way.
When we have just one remaining category, we don't need to type out all the other product names.
We'll simply add, cheap, without a corresponding product name.
This last argument is a default value, which is assigned to any product, which has not already been classified as expensive, or mid range.
It should now be much easier to understand how the switch function works.
At each row of the price range column the function searches the list we provided for the relevant product name. If it finds the product, it assigns the corresponding value to the price range field.
Otherwise, it assigns the default value of, cheap.
Let's press enter to create the column, and look at this field in a pivot chart.
We'll go to the pivot chart from the previous lesson, copy it, create a new sheet, and past the chart.
We'll remove product size as a legend, and replace it with price range.
We can see there are three expensive products, three mid range products, and the remainder are cheap, exactly as we would've expected. Switch functions are obviously not as flexible as the if functions we saw in previous lessons. If you need to make numeric comparisons, if statements are a better option.
Switch functions are most useful when you're working with discreet data fields, such as the product name, in this instance, and you have a relatively small number of categories.
In these cases, a switch function can be a good way of categorizing data.
In the next lesson, we'll look at how to compare values, and create rankings with DAX.