Forum Discussion
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)
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:
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.
6 Replies
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=czNHt7UXIe8amitchandak thankyou, here is the PBIX link, (i couldn't find a way to upload directly)
https://drive.google.com/drive/folders/1GBZT7YqtHiUBPGouioHqn8yMA4m1XCxm?usp=sharing
Hi, taspland
I can’t download the sample file from your google link and it says that I need access:
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
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:
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.
v-robertq-msft thankyou for your help, that solved my issue.
