DAX for Power BI Part 2.3 - The Switch Function

DAX for Power BI Part 2.3 - The Switch Function Welcome to this weizel dax4 power bi tutorial in this part of the series we'll take a look at how to use the switch function so we'll start as you might expect by.

Looking at some basic switch expressions which allow you to compare the values of a column against a list of constants we'll then explain how you can modify your switch functions so that you can.

Write proper logical tests and explain the importance of the order in which you write those logical tests to get the correct results in the final part of the video.

We'll take a quick look at how to use a switch function to create a sort by column so let's get started to get started i've created a new blank.

Report and as usual the first thing i'll do in here is import some data from an excel workbook just for a change i'll click on the import data from excel button on the.

Canvas and once the dialog box launches and i've navigated to the folder containing the file i want to import i can double click on that to launch the import wizard.

DAX for Power BI Part 2.3 - The Switch Function

Again i'll drop a link in the video description so you can download this file yourself once the dialog box is opened i can check the box next to the movies.

Worksheet then click the load button to import all the data from that sheet into the reports data model once that has loaded so that we can see the results of any expressions we create.

Let's head into the data view and then we're ready to start writing our first switch expression for the first example i'd like to create a label for each film based on the value.

Of the oscar wins column so i'd like to say things like if the oscar wins equals zero the film is a loser if the oscar wins is one it's a one-hit wonder if it's two it's a double winner and so on.

So i basically want to test the individual specific values in the oscar wins column and produce a unique result for each one now if you watched the previous video in.

The series you may be thinking we could do that using a sequence of nested if functions and yes we could but hopefully you're also thinking if you watched the previous video in the series that that.

    Would be a terrible idea the expression

    We'd have to write would be really awkward to create and maintain and make changes to later for this sort of situation a switch.

    Function is a much better choice so let's create our new column to start with if i click the new column button on the table tools tab in the ribbon and then i can zoom in on the formula bar.

    Either by holding the ctrl key and rolling my mouse wheel forwards or ctrl and the plus key or the equals key to zoom in let's create a.

    More sensible column name i'm going to call this one win label and then after the equals operator type in sw to highlight the switch function hit the tab key to enter the rest of the.

    Function name and open the round brackets and then press shift and enter to head down to the next line where we need to start by specifying an.

    Expression containing the values we're going to test for so the expression in this case is going to be a reference to the oscar wins column so let's start typing in oscar.

    Wins and then hit tab to get the rest of that typed in then we can type in a comma shift and enter to create a new line.

    And then we specify the first value we want to test for in that column so i'm simply going to write the value 0 and then follow that with a comma and on the same line i'm going to write.

    The result i want to return if the value of the oscar wins column is zero so in some double quotes i'm going to call that film a loser so at this point it's a lot like a very.

    Basic if function we've effectively

    Written if the oscar wins column equals zero then provide the answer loser so you can see we've specified the first three parameters of the switch function.

    Everything past that is optional just to demonstrate that i'm going to press shift and enter and then close the round brackets and then press enter to create my formula and you'll see that.

    Any film which has zero for the oscar wins is indeed described as a loser but the further down we scroll when we get to the films which have won oscars they're provided with a blank.

    So far we haven't really done anything we couldn't have achieved with a basic if function but the beauty of a switch function is how easy it is to add multiple possible.

    Conditions and answers if we click back inside the expression and then at the end of the word loser type in another comma you'll see that when the tooltip returns it's gained.

    Some extra parameters value 2 and result 2. if i hit shift and enter to go down to the next line type in the next value i want to test for the number 1 followed.

    By a comma and then in some multiple quotes i can type in a description for films whose oscar wins value equals one so for one hit wonder if i type in a further comma you'll see.

    That i've expanded the tooltip again to have a value three and result three if i hit shift and enter i can do the number two followed by another comma and then i can in some double quotes let's call.

    These double winners and again at this point or double winner i should say so at this point if i press enter to update my expression we'll see that all the zero oscar.

    Winners are still described as losers but when i get down far enough we ought to encounter some one-hit wonders and some double winners down there as well now at some point possibly already.

    In fact you're going to get bored of testing for specific values and you'll just want to group everything else together in one final category so at the moment anything which hasn't been.

    Explicitly tested for zero one or two currently has a blank i'd like any of those films to be described as a multi-winner so to do that we can make use of the.

    Else parameter of the switch function if i type in a comma at the end of the final result i've created press shift and enter and then just enter the value i want to return so i'm.

    Going to enter multi winner close a double quotes and then press enter if i now scroll down again anything which hasn't been captured by one of.

    Those three explicit values is now described as a multi-winner so that was a fairly simple example where we're comparing the value of a column against the list of specific.

    Constants for the next example let's try something a little more complicated i want to group my films based on their runtime into groups called short medium long and.

    Epic and in order to do that i don't want to have to write out every single possible runtime value i might encounter in that column as the saying goes ain't nobody.

    Got time for that so instead what we're going to do is write a sensible logical test we'll begin by checking if the run time is less than 100 and if so we'll describe.

    The film as short so let's create a new column and i'm going to call this one length and then say equals switch open the round brackets and then.

    Shift and enter then we're a little bit stuck about what to write here if we just start doing this by following the example we created earlier.

    If i refer to my movie's runtime column first of all and then type in a comma and then on the next line i try to say is that less than 100.

    We'll find that we run into a syntax problem here you'll see the red squiggly underneath the less than symbol whenever you're using comparison operators like a less than greater than.

    Etc you must have a value on either side of the comparison operator to be compared so this syntax is not correct to make the syntax correct i guess what i could do is say movies runtime or in.

    Fact i'll just say runtime and then get the movies keyword filled in for me so now this is a valid logical test it's in the movies runtime less than 100. i could then type in a comma and then in.

    Some double quotes write the word short and then i could close the round brackets and press enter but then i'll be a little bit upset to find that that returns an error.

    Now the reason this returns an error is because of what this expression returns when you're writing logical tests the only two possible results are true or false as this little error message here.

    Describes the expression you write here for the values in a switch function are compared with the initial expression you write so i'm trying to compare true or false.

    With a number so this isn't going to work what i need to do is change the initial expression to match the possible return values of our logical.

    Tests so i want to check the expression true so i'll type in the word true hit the tab key to type in the rest of that and get the open and close round brackets.

    DISCLAIMER: In this description contains affiliate links, which means that if you click on one of the product links, I'll receive a small commission. This helps support the channel and allows us to continue to make videos like this. All Content Responsibility lies with the Channel Producer. For Download, see The Author's channel. The content of this Post was transcribed from the Channel: https://www.youtube.com/watch?v=_lA17VBi9Bk
Previous Post Next Post