Switch formula with blank and multiple conditions

[Anonymous]

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:

Thank you in advance!

[AlexisOlson]

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"
    )
)

[Anonymous]

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",

)

[AlexisOlson]

Using IN works fine. How about this?

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"
)

[Anonymous]

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)?

[AlexisOlson · Accepted solution]

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"
)

[Anonymous]

Works great! Thank you!