Ninth Day of Christmas: DAX function SWITCH

 🎵 On the ninth day of Christmas, my true love gave to me... 

Meaningful Switches, 

Syncing Slicers Slicing,

Conditional Drill-through Button,

Verified User Selections, 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Conditional Statements: DAX IF() Function

The IF function is really powerful, and since it's also an Excel function many of us already know how to use it. In fact, we've used it before in this 12 Days of Christmas series. Let's do a quick review of how the IF function works. It takes three arguments: 

1. Logical test (required)
2. Value if true (required)
3. Value if false (optional)

We can see that IF allows us to ask a question (aka logical test) that has exactly two responses: True or False. This worked well when we were checking if the SantaList[Name] column was filtered to exactly one value. HASONEVALUE function returns only two choices - True or False and that works perfectly with the IF function. 

It can work in other cases where only two choices exist, for example Santa's List only has two options: 

1. Naughty
2. Nice

That means we can write a measure that returns a number based on the status of the name (we can use this for conditional formatting if it's a number): 

Icon IF =
IF (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ) = "Nice", 
    1,
    0
)

Then we can apply conditional formatting to our visualizations. Like I've said before, conditional formatting has permeated many areas of Power BI, so you'll get good at it before too long. 

However, on second thought, we actually have three options: 

1. Naughty
2. Nice
3. No selection

A single IF statement cannot handle three cases, but SWITCH can. (Yes, I know nested IF statements can too, but they're messy.)

Conditional DAX Function: SWITCH

The SWITCH function allows us to provide as many options (aka cases) as we want. The SWITCH function takes at least three arguments: 

1. Expression (required)
2. Value1 (required)
3. Result1 (required)
4. Value2 (optional)
5. Result2 (optional)
6. ValueN (optional)
7. ResultN (optional)
8. ResultElse (optional)

There are lots of Value and Result arguments floating around, so this can be a confusing function the first time you see it. Let's see what our Icon measure looks like with SWITCH:

Icon =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", 1,
    "Naughty", 0,
    -1
)

Note I have used the Shift key and Enter key on the keyboard to put Result1 ("Nice") and Value1 (1) on their own line. This makes it easy to see that -1 is the ResultElse. If the selected value in the Naughty or Nice column is not Nice or Naughty, then this measure will return -1. If the ResultElse is left blank, then the measure will return BLANK(). 

Now that we know how SWITCH works, we can write a few functions to add more meaning to our report. 

How would you feel if you were on the naughty list?

Emotion =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Laugh",
    "Naughty", "worried",
    "normal"
)

What would Santa say to someone on the Nice list?

Letter =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Looks like you have been nice in 2020. Keep up the good work!",
    "Naughty",
        "Looks like you have been naughty in 2020. Best change your ways before Christmas.",
    "Pick a name."
)

What pose would you make if you found out you were on the Nice list?

Pose =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Yuhoo",
    "Naughty", "AngryFrustrated",
    "HandsFolded"
)

How to:

1. Open the SantaList.pbix file.
2. Create new measures as above for:
1. Icon
2. Emotion
3. Letter
4. Pose
3. Create a Card visual on the 'Check the List' page using the [Letter] measure.
4. Select the table visual from the 'Summary' page.
5. It should have SantaList[Name] column in values.
6. Click the down arrow next to the [Name] column in the values > Conditional Formatting > Icons
7. Use the [Icon] measure we created above to apply conditional formatting.
8. Pick appropriate icons.
9. Click OK.
10. Save the report.

Cool! You've just becoming a coding guru. SWITCH functions are a handy tool to have in your toolkit, so keep practicing them. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at the Comicgen custom visual and see how those poses and emotions will come into play.