cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Switch formula with blank and multiple conditions

Hello,

I have a formula I am trying to clean up and it has me stumped. The original formula is:

Reason Codes =

IF('Version'[This vs That] = "That",
IF('Version'[Reason]= "0","No Exception",
IF('Version'[Reason]= BLANK(),"No Exception",
IF('Version'[Reason]="1","Error",
IF('Version'[Reason] = "2","Good",
IF('Version'[Reason]= "3","Other",

)))))

I have created the following switch formula:

Reason Code =

SWITCH (

TRUE (),
'Version'[Reason] IN { 0 }, "No Exception",
'Version'[Reason] IN { 1, 01 }, "Error",
'Version'[Reason] IN { 2, 02 }, "Good",
'Version'[Reason] IN { 3, 03 }, "Other",
)
But I'm hitting walls on adding in blanks & taking that first If formula into the equation.

Table example:

1 ACCEPTED SOLUTION
Super User

Like this? (Change the order as needed.)

``````Reason Code =
SWITCH (
TRUE (),
ISBLANK ( 'Version'[Reason] ), "No Exception",
'Version'[This vs That] <> "That", BLANK (),
'Version'[Reason] IN { 0 }, "No Exception",
'Version'[Reason] IN { 1, 75 }, "Error",
'Version'[Reason] IN { 2, 51 }, "Good",
'Version'[Reason] IN { 3, 100 }, "Other"
)``````

6 REPLIES 6
Super User

You can do it in one big SWITCH

``````Reason Codes =
SWITCH (
TRUE(),
'Version'[This vs That] = "That" && 'Version'[Reason] = "0", "No Exception",
'Version'[This vs That] = "That" && 'Version'[Reason] = BLANK(), "No Exception",
'Version'[This vs That] = "That" && 'Version'[Reason] = "1", "Error",
'Version'[This vs That] = "That" && 'Version'[Reason] = "2", "Good",
'Version'[This vs That] = "That" && 'Version'[Reason] = "3", "Other"
)``````

Or only apply the switch to the Reason part:

``````Reason Codes =
IF (
Version[This vs That] = "That",
SWITCH (
Version[Reason],
BLANK (), "No Exception",
"0", "No Exception",
"1", "Error",
"2", "Good",
"3", "Other"
)
)``````
Regular Visitor

OK, but as the data set gets larger, it will be become just as large as the old dax formula. I was hoping to have something to tighten up the code.

Ex:

Reason Code =

SWITCH (

TRUE (),
'Version'[Reason] IN { 0 }, "No Exception",
'Version'[Reason] IN { 1, 75 }, "Error",
'Version'[Reason] IN { 2, 51 }, "Good",
'Version'[Reason] IN { 3, 100 }, "Other",
)

Super User

``````Reason Code =
SWITCH (
TRUE (),
'Version'[This vs That] <> "That", BLANK (),
'Version'[Reason] IN { 0 }, "No Exception",
'Version'[Reason] IN { 1, 75 }, "Error",
'Version'[Reason] IN { 2, 51 }, "Good",
'Version'[Reason] IN { 3, 100 }, "Other"
)``````
Regular Visitor

Is there a way to make blank a no exception because as shown in the example (and the larger actual dataset) there are quite a few blanks (infuriating but here we are)?

Super User

Like this? (Change the order as needed.)

``````Reason Code =
SWITCH (
TRUE (),
ISBLANK ( 'Version'[Reason] ), "No Exception",
'Version'[This vs That] <> "That", BLANK (),
'Version'[Reason] IN { 0 }, "No Exception",
'Version'[Reason] IN { 1, 75 }, "Error",
'Version'[Reason] IN { 2, 51 }, "Good",
'Version'[Reason] IN { 3, 100 }, "Other"
)``````

Regular Visitor

Works great! Thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors