Value needs to be passed from table to Query

[Tarak_G]

Hi Team,

I've two tables . one table is Incidents and another one : SLA Duration table

Need to calculate whether incident is WithInSLA or OutOfSLA 

SLA Table

Time to Acknowledge	No		SupportHours	90
Time to Mitigate	No	0	SupportHours	360
Time to Mitigate	No	1	SupportHours	360
Time to Mitigate	No	2	SupportHours	480
Time to Mitigate	No	3	SupportHours	4320
Time to Mitigate	No	4	SupportHours	4320
Time to Resolve	No	0	SupportHours	7200
Time to Resolve	No	1	SupportHours	7200
Time to Resolve	No	2	SupportHours	7200

For Example: -- This  Duration needs to be passed from sla Duration  table based on severity  && KPI= TimeToMitigate && severiry 0,1 && Value =Duration i.e,360

Calculated Column has been written in the attached pbix file :

TimeToMitigate_SLA = IF ( OR(Incidents[Severity]=0,Incidents[Severity]=1) &&

Incidents[TTM] <=360  -- Need to Pass value from table based on severity and Kpi= TimetoMitigate

&&

Incidents[Severity]=2 &&Incidents[TTM] <=480 &&

OR(Incidents[Severity]=3,Incidents[Severity]=4) && Incidents[TTM] <= 4320,

"With In SLA","Out Of SLA")

Here is the link: https://1drv.ms/u/s!Au-aOkl1BoHugwWDBlLY9377Govp?e=fT2h5z

Thanks,

Tarak

[v-kelly-msft · Accepted solution]

Hi @Tarak_G ,

Not quite sure whether I have caught your point,you said :Need to Pass value from table based on severity and Kpi= TimetoMitigate,do you wanna create a column to get the value returned from "SLA Table"?

Then you could create a column as below:

_Duration = CALCULATE(MAX('SLATime'[Duration]),FILTER('SLATime','SLATime'[Severity]='Incidents'[Severity]&&'SLATime'[KPI]="Time to Mitigate"))

And you will see:

If I misunderstood your meaning,pls let me know.

 For the related .pbix file ,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Tarak_G , what do mean by query here?

a measure or a new column in another table?

Copy value from one table to another - https://www.youtube.com/watch?v=czNHt7UXIe8

[Tarak_G]

Hi @amitchandak ,

Here query means calculated column and attched pbix file.  I tried with lookupvalue but i was not able to get it. Can you help me on that ?

[amitchandak]

@Tarak_G , Still not clear, But a new column like this in Incident table

Column = SWITCH(TRUE(), ISBLANK([TTM]) , "Not Done" ,[TTM] > maxx(FILTER(SLATime, SLATime[KPI] ="Time to Mitigate"  && SLATime[Severity] = Incidents[Severity] ),SLATime[Duration]), "Out of SLA", "In SLA")