Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
10 months ago
Solved

IF Statement or Switch

I've read that Switch statements are more efficient and less costly than IF statements so I'm trying to convert this code to Switch

but I'm not sure how I can avoid the IF at the beginning?


Days to Validation Bins = 
IF(AND('Cases'[Created On] < DATEVALUE("01/04/2020"), 'Cases'[statecode] = "Resolved" || "2"),BLANK(),
    IF(ISBLANK('Cases'[Days to Validation]),BLANK(),
        IF( 'Cases'[Days to Validation] <= 30 , "0-1 Mth",
            IF('Cases'[Days to Validation] <= 60, "1-2 Mths",
                IF('Cases'[Days to Validation] <= 90, "2-3 Mths", 
                    "Older")
))))

 

Need Help
Show and Tell
  • mh2587's avatar
    mh2587
    10 months ago
    Days to Validation Bins = //Try this
VAR _Created = 'Cases'[Created On]
VAR _State   = 'Cases'[statecode]
VAR _Days    = 'Cases'[Days to Validation]
RETURN
SWITCH(
    TRUE(),
    AND(_Created < DATE(2020,4,1), OR(_State = "Resolved", _State = "2")), BLANK(),
    ISBLANK(_Days), BLANK(),
    _Days <= 30, "0-1 Mth",
    _Days <= 60, "1-2 Mths",
    _Days <= 90, "2-3 Mths",
    "Older"
)

3 Replies

  • mh2587's avatar
    mh2587
    Super User
    10 months ago
    Days to Validation Bins = //Try this
VAR _Created = 'Cases'[Created On]
VAR _State   = 'Cases'[statecode]
VAR _Days    = 'Cases'[Days to Validation]
RETURN
SWITCH(
    TRUE(),
    AND(_Created < DATE(2020,4,1), OR(_State = "Resolved", _State = "2")), BLANK(),
    ISBLANK(_Days), BLANK(),
    _Days <= 30, "0-1 Mth",
    _Days <= 60, "1-2 Mths",
    _Days <= 90, "2-3 Mths",
    "Older"
)
  • danextian's avatar
    danextian
    Super User
    10 months ago

    Hi ArchStanton 

    One thing's for sure: SWITCH is less verbose and easier to read

    Days to Validation Bins =
SWITCH(
    TRUE(),
    ISBLANK('Cases'[Days to Validation]) ||
    (
        'Cases'[Created On] < DATE(2020, 4, 1) &&
        'Cases'[statecode] IN {"Resolved", "2"}
    ), BLANK(),

    'Cases'[Days to Validation] <= 30, "0-1 Mth",
    'Cases'[Days to Validation] <= 60, "1-2 Mths",
    'Cases'[Days to Validation] <= 90, "2-3 Mths",
    "Older"
)