SWITCH It Up

In order to master managing data models, learning how to leverage conditional logic is a must. We see conditional logic in Excel functions like the IF function. However, those who use Excel a lot will know how cumbersome nesting conditional statements within the function become with multiple conditions. One way to streamline this process is by using the newer IFS function in Excel. Here's one way that I visualize the difference between the IF and IFS functions.

Another way that I visualize the logic for the {if, else if, else} conditions is a bit like combinatorics or sorting algorithms. If we focus on a single row within a data table, whatever condition that row first meets determines the result that it returns. Once we assign a row a result, it's taken out of the remaining rows for the next condition and the conditions after that we can assign it to. This means that with each additional condition, there are typically fewer rows still waiting for their result assignments. We can then set a catch-all condition with the else result.

We can also tap into the SWITCH function in Excel and Power BI to set up multiple conditions. Let's explore two ways to use the SWITCH function in DAX measures in Power BI!

SWITCH Option 1

The first way uses exact conditions, as we can see in the example lesson below. I also use the FLOOR and CEILING functions in conjunction with the SWITCH function.

SWITCH Option 2

We can also use SWITCH for inequalities in conditional logic. I explore how to do this in one of my latest Power BI Weekly videos!

Comparing SWITCH Options

So how do these functions compare? Here's a graphic comparing both of them in example DAX measure functions. It's a high-level overview of the Power BI Weekly videos above, so check those out first!

Coming Up

I have two courses coming up in the pipeline for release with LinkedIn Learning. Here's a sneak peek snapshot of what I'm stitching together!

Also, stay tuned for the latest Power BI Weekly videos (I share them every week when they come out). If you live in Houston, check out our monthly user group meeting at the Microsoft offices in City Centre (I'll post exact details on the next meeting when we confirm them).

-HW