Let’s learn how to use a fun little DAX function called SWITCH.

The SWITCH function does just what it sounds like. It switches (see?) one value/thing for another value/thing. Or a list of values/things for a list of other values/things.

We’re going to use a simple Power BI file to get to know how to do this. If you’d like to use the same file, you can download it here:

Video Post:

Written Post:

We’ve got a nice simple report here with a simple table built to show our data.

If you are in a scenario where you need to group these countries into groups (in this case, we’ll use continents) it’s totally possible. You may be in a situation where you don’t own the data source and can’t incorporate groupings into it, so you need to do it in Power BI.

One way to group items would be to use a Nested IF function (which you can learn about here), where you write some logic that says, basically “If this ONE thing is true, then show ABC value. If it’s not true, check if this OTHER thing is true… if THAT is true, show XYZ value. If THAT isn’t true, check to see if this OTHER OTHER thing is true. If it is, show 123 value….

… and so on and so on. It can get REAL complicated, REAL fast.

The SWITCH function can simplify this process a lot. Let’s try it out.

We’ll right-click on our data in our Data Pane and select New column.

In the formula bar that appears above our canvas, we’ll write this DAX:

CONTINENT = 
SWITCH('Table'[Country], 
    "Algeria", "Africa",
    "Canada", "North America", 
    "Nigeria", "Africa", 
    "The Netherlands", "Europe", 
    "United Kingdom", "Europe", 
    "United States", "North America"
)

This DAX basically has 2 parts (although it looks like far more than 2).

The first part is where we have our Country column as the first thing in our code, right after the SWITCH function. This is the part that identifies *where* the value/item is we are looking for.

Then after that, we have a series of pairs of items. The 1st item in each pair is what we want Power BI to look for in the Country column. The 2nd item in each pair is what we want Power BI to replace our Countries with.

So, if it finds Algeria, it returns Africa. If it finds United Kingdom, it returns Europe.

We can add this new column to our table, to make sure it’s working:

If you decide to take out the Country column, your values will be grouped by Continent.

Nice and easy right? It’s a bit more manageable way to group things without using a Nested IF statement.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.