Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to 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,
Proud to be a Super User! | |
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,
Proud to be a Super User! | |
Oh wow, you sir are an absolute genius! Thank you so much, this is perfect.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
79 | |
67 | |
60 | |
45 | |
45 |