Sign in or start a free trial to avail of this feature.
4. CASE() Function
The CASE() function is a sometimes a better alternative to IF/ELSEIF when dealing with discrete sets of data. We will learn how to use the CASE() function in this lesson.
To explore more Kubicle data literacy subjects, please refer to our full library.
Using the CASE() function
- A faster alternative to IF/ELSE when checking discrete data
- Accepts discrete fields as inputs only
- Less flexible than IF/ELSE as CASE() cannot work with AND/OR functions
Case functions are simplified versions of the IF ELSEIF formulas that we saw in the previous lesson. Case functions are faster than If ELSE, but they do not have the same flexibility when creating conditions, with functions such as NOT and AND, OR. Instead, case can only check if the exact value in a column is present. Let's see the Case function in action, with a quick example. I'm going to create a new calculated field.
And I'll call it Sample Case Function.
Say, I want to create another product categorization, but I don't want to base it on sales. Instead, I want to base it on products that I know to be either expensive, mid range, or cheap. To do this, I'm going to use the Case function. And the Case function is only going to check one column. Which is going to be the product name column.
And in this column, I know that three products are going to be considered expensive, three are considered mid range, and the remainder are going to be considered cheap.
So when creating my Case function, I start by saying, when, "Byresta" which is going to be an expensive product, then, "expensive." And this means when Byresta appears in a row, this sale is going to be considered expensive. Next, when "Hydrabak," then, "expensive." When "Polinio," then "expensive." These represent the three expensive products. Off-camera, I'll add in the three products considered mid range on price.
All I need to do now, is account for the remaining products. Which are all going to be considered cheap. And to do this, I can write, else "Cheap" and then END to finish the function.
As you can see, the Case function is pretty easy to write. First, we specified the field that will be checked by case. And then we add in the values that we want to extract from that field, including Byresta, Polinio, Bestane, et cetera.
When Byresta appears in a row, then we assign it a value, such as expensive. And we do this for each of the expensive products, and for the mid range products, allowing "Cheap" to represent all remaining values. Because Case functions need to reference an exact value in a column, we typically use them in dimensions, such as product names, rather than in measures that contain continuous numbers or dates. In certain cases, like in this example, they provide better performance than IF ELSEIF and should be used. However, in cases where you want to apply conditions to numbers, such as greater than or less than, using IF and ELSE is often the better option. Let's now see this new calculated field in action. I'll update its name, and I'll call it price profile, and press Okay.
I'll duplicate this sheet.
I'll remove my previous color code, and add in price profile. And now we have our sales byproduct, but color-coded by price profile.