cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taspland
Frequent Visitor

SLA Calculation

Hi, i'm trying to get a correct SLA state however i'm unable to get the data to show correctly. and have no idea how to split the incident and task sla on 

I have a table with

Ticket ID, Ticket Type (task or Incident) Business Duration (in seconds), Priority.   then trying to create a calculated column

Priority            incident sla Task SLA
4 - Normal       864000
3 - High             172800 259200
2 - Critical           100800 86400
1 - Very Critical 7200 7200

i also have another table with the SLA's (in seconds)

Capture.JPG

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @taspland 

I’ve downloaded your pbix file and I can roughly understand your requirement according to your description, you can try this calculated column:

SLA STATE =

VAR _incidentsla=RELATED(SLA[incident sla])

VAR _tasksla=RELATED(SLA[Task SLA])

Return

IF(

    [ticket type]="Incident",

    IF(

        [business_duration]>=_incidentsla,

        "SLA Violated","SLA OK"),

        IF(

        [business_duration]>=_tasksla,

        "SLA Violated","SLA OK")

)

The output is like this:

v-robertq-msft_0-1621390117592.png

 

And you can get what you want.

You can download my test pbix file below

 

If you still have a problem, you can explain your expected result in detail with examples.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-robertq-msft
Community Support
Community Support

Hi, @taspland 

I’ve downloaded your pbix file and I can roughly understand your requirement according to your description, you can try this calculated column:

SLA STATE =

VAR _incidentsla=RELATED(SLA[incident sla])

VAR _tasksla=RELATED(SLA[Task SLA])

Return

IF(

    [ticket type]="Incident",

    IF(

        [business_duration]>=_incidentsla,

        "SLA Violated","SLA OK"),

        IF(

        [business_duration]>=_tasksla,

        "SLA Violated","SLA OK")

)

The output is like this:

v-robertq-msft_0-1621390117592.png

 

And you can get what you want.

You can download my test pbix file below

 

If you still have a problem, you can explain your expected result in detail with examples.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-robertq-msft thankyou for your help, that solved my issue. 

v-robertq-msft
Community Support
Community Support

Hi, @taspland 

I can’t download the sample file from your google link and it says that I need access:

v-robertq-msft_0-1621306099144.png

 

But according to your DAX formula, I can roughly understand your requirement, you can try to change the formula of the calculated column like this:

SLA STATE=

VAR _sla=Calculate(MAX(SLA[inc sla]),Filter(All(SLA),[P]=Earlier(task[priority])))

Return

IF(

      Task[business_duration]>=_sla,

      “SLA Violated”,

      “SLA OK”)

 

If this result is not what you want, you can give me the access to your google link and your explain your expected result with examples in detail.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-robertq-msft thankyou for your assistance, i have provided you with access. i'm getting results that doesn't make sense, if you look below you can see it's showing SLA breached for time less than SLA Met, i think it maybe something with Incident vs tasks?  these below were incidents 

 

Capture.JPG

taspland
Frequent Visitor

@amitchandak thankyou, here is the PBIX link, (i couldn't find a way to upload directly)

https://drive.google.com/drive/folders/1GBZT7YqtHiUBPGouioHqn8yMA4m1XCxm?usp=sharing

Google drive link 

 

amitchandak
Super User
Super User

@taspland , Table structure is not clear. You need something like this

New column =
var _cnt = countx(filter(SLA, SLA[Priority] = ticket[Priority] && ticket[Business Duration in seconds] <= SLA[incident sla]),SLA[Priority])
return
if(isblank(_cnt), "SLA OK", "Not OK")

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

This can help

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!