Switch Function Help

[Auski]

Hi Guys, 

I need to make a tweek to a custom column created a couple of weeks ago. Previously I had to determine if a ticket had met or missed SLA based on the duration the ticket was open for. Now I need to modifiy the code to determine if the SLA has missed if case is not closed.

To do this, I have calculated the business days between the case being opened and the last refresh date (if the case is still open).

I used the following switch function, but I need someone much smarter than me to help me understand why it hasnt worked.

SLA Met_Fail =

SWITCH (

    TRUE (),

    ISBLANK ( 'mart case'[SLA] ), BLANK (),

    'mart case'[SLA] = "60 Mins" && [Age_Minutes] <= 60, "SLA Met",

    'mart case'[SLA] = "60 Mins" && [Bus Hours Not Closed] <= 60, "",

    'mart case'[SLA] = "12 Hours" && [Age_Hours] <= 12, "SLA Met",

    'mart case'[SLA] = "12 Hours" && [Bus Hours Not Closed] <= 12, "",

    'mart case'[SLA] = "24Hours" && [Age_Hours] <= 24, "SLA Met",

    'mart case'[SLA] = "24Hours" && [Bus Hours Not Closed] <= 24, "",

    'mart case'[SLA] = "2 Business Days" && [Business Days Open] <= 2, "SLA Met",

    'mart case'[SLA] = "2 Business Days" && [Business Days Not Closed] <= 2, "",

    'mart case'[SLA] = "3 Business Days" && [Business Days Open] <= 3, "SLA Met",

    'mart case'[SLA] = "3 Business Days" && [Business Days Not Closed] <= 3, "",

    'mart case'[SLA] = "4 Business Days" && [Business Days Open] <= 4, "SLA Met",

    'mart case'[SLA] = "4 Business Days" && [Business Days Not Closed] <= 4, "",

    'mart case'[SLA] = "5 Business Days" && [Business Days Open] <= 5, "SLA Met",

    'mart case'[SLA] = "5 Business Days" && [Business Days Not Closed] <= 5, "",

    'mart case'[SLA] = "10 Business Days" && [Business Days Open] <= 10, "SLA Met",

    'mart case'[SLA] = "10 Business Days" && [Business Days Not Closed] <= 10, "",

    'mart case'[SLA] = "Action Date" && [Action Date Resolution] = "Y", "SLA Met",

    "SLA Fail"

)

As you might be able to tell from the screenshot below, all of the following are still open and Bus Days Not Closed are all over SLA Duration. The first row as an example has an SLA of 10 days. The ticket is still open is is now 11 business days old. This should have been labelled as "SLA Failed".


I am trying to have 1 column the tells me whether the SLA has met, SLA has failed or SLA has not yet met or failed (Blank).

Hope that makes sense. I am unable to provide data sample sorry. Is there something glaringly obvious I have overlooked?

Thanks so much for looking at this.
Regards

[rajendraongole1 · Accepted solution]

Hi @Auski - Your DAX formula needs an adjustment to correctly classify the SLA status based on whether a case is open and has exceeded the SLA duration. 

you can add below 

SLA Met_Fail =
SWITCH(
TRUE(),
ISBLANK('mart case'[SLA]), BLANK(),

// SLA Met conditions for closed cases
'mart case'[SLA] = "60 Mins" && 'mart case'[Status] = "Closed" && [Age_Minutes] <= 60, "SLA Met",
'mart case'[SLA] = "12 Hours" && 'mart case'[Status] = "Closed" && [Age_Hours] <= 12, "SLA Met",
'mart case'[SLA] = "24 Hours" && 'mart case'[Status] = "Closed" && [Age_Hours] <= 24, "SLA Met",
'mart case'[SLA] = "2 Business Days" && 'mart case'[Status] = "Closed" && [Business Days Open] <= 2, "SLA Met",
'mart case'[SLA] = "3 Business Days" && 'mart case'[Status] = "Closed" && [Business Days Open] <= 3, "SLA Met",
'mart case'[SLA] = "4 Business Days" && 'mart case'[Status] = "Closed" && [Business Days Open] <= 4, "SLA Met",
'mart case'[SLA] = "5 Business Days" && 'mart case'[Status] = "Closed" && [Business Days Open] <= 5, "SLA Met",
'mart case'[SLA] = "10 Business Days" && 'mart case'[Status] = "Closed" && [Business Days Open] <= 10, "SLA Met",

// SLA Met conditions for open cases
'mart case'[SLA] = "60 Mins" && 'mart case'[Status] <> "Closed" && [Bus Hours Not Closed] <= 60, "SLA Met",
'mart case'[SLA] = "12 Hours" && 'mart case'[Status] <> "Closed" && [Bus Hours Not Closed] <= 12, "SLA Met",
'mart case'[SLA] = "24 Hours" && 'mart case'[Status] <> "Closed" && [Bus Hours Not Closed] <= 24, "SLA Met",
'mart case'[SLA] = "2 Business Days" && 'mart case'[Status] <> "Closed" && [Business Days Not Closed] <= 2, "SLA Met",
'mart case'[SLA] = "3 Business Days" && 'mart case'[Status] <> "Closed" && [Business Days Not Closed] <= 3, "SLA Met",
'mart case'[SLA] = "4 Business Days" && 'mart case'[Status] <> "Closed" && [Business Days Not Closed] <= 4, "SLA Met",
'mart case'[SLA] = "5 Business Days" && 'mart case'[Status] <> "Closed" && [Business Days Not Closed] <= 5, "SLA Met",
'mart case'[SLA] = "10 Business Days" && 'mart case'[Status] <> "Closed" && [Business Days Not Closed] <= 10, "SLA Met",

// SLA Failed conditions
'mart case'[SLA] = "60 Mins" && [Bus Hours Not Closed] > 60, "SLA Fail",
'mart case'[SLA] = "12 Hours" && [Bus Hours Not Closed] > 12, "SLA Fail",
'mart case'[SLA] = "24 Hours" && [Bus Hours Not Closed] > 24, "SLA Fail",
'mart case'[SLA] = "2 Business Days" && [Business Days Not Closed] > 2, "SLA Fail",
'mart case'[SLA] = "3 Business Days" && [Business Days Not Closed] > 3, "SLA Fail",
'mart case'[SLA] = "4 Business Days" && [Business Days Not Closed] > 4, "SLA Fail",
'mart case'[SLA] = "5 Business Days" && [Business Days Not Closed] > 5, "SLA Fail",
'mart case'[SLA] = "10 Business Days" && [Business Days Not Closed] > 10, "SLA Fail",

// Special condition for "Action Date"
'mart case'[SLA] = "Action Date" && [Action Date Resolution] = "Y", "SLA Met",

// Default
"SLA Pending"
)

This revised formula should handle both open and closed cases correctly,

[Auski]

Oh wow, you sir are an absolute genius! Thank you so much, this is perfect.