Condition to proactively predict escalation

[prasadhebbar315]

Hello everyone,

I am working on a solution to identify if ML model predicted escalation before it was escalated.

Data has Serial Number, Escalated Date, Prediction_date, Prediction_Category. Condition to calculate "Prediction_Before_Escalation" is that for a SN, the Prediction_Date should be less than or equal to Escalated Date and the Prediction_Category should be "High or Critical". SN_Prediction_Before_Escalation condition is at least once for a SN, the Prediction_Date should be less than or equal to Escalated Date and the Prediction_Category should be "High or Critical". Please note that Escalation_Date is in a different table and Prediction_Date is in another table. Both tables are related on Serial Number. The IF condition should also specify relationships.

In the below table Prediction_Before_Escalation is No for 4642531975 since Prediction_Date is after Escalation Date even though Prediction_Category is High. Also for SN: GM03WRG1 there is one instance of No but the prediction has happened at least once before escalation and hence the last column is marked as Yes. 

For SN: AAE120MP the prediction has happened before escalation but the category is not "High or Critical" hence "No"

serialnumber	Escalated_Date	Prediction_Date	Prediction_Category	Prediction_Before_Escalation	SN_Prediction_Before_Escalation
78127344	3/23/2023	3/23/2023	Critical	Yes	Yes
78127344	3/23/2023	3/21/2023	Critical	Yes	Yes
4642531975	4/24/2023	4/25/2023	High	No	No
AAE120MP	4/11/2023	4/11/2023	Moderate	No	No
AAE120MP	4/11/2023	4/10/2023	Moderate	No	No
AAE120MP	4/11/2023	3/31/2023	Low	No	No
GM029LQD	4/14/2023	4/14/2023	Critical	Yes	Yes
GM03WRG1	4/12/2023	4/14/2023	Critical	No	Yes
GM03WRG1	4/12/2023	4/5/2023	Critical	Yes	Yes

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc89CoAwDAXgu3QutC9JrY6C0kVBXRzE+1/Dopbq4g9kePA+QrIsypcgzyJKKzYUxxLf8qqfEDKSQsgxKu9iJYYksRhcZnXdgmw/7AWQEfAF2VfEhi+bQm+p6sbmQJebUj4Rz1PAUdBPlJ5bNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [serialnumber = _t, Escalated_Date = _t, Prediction_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Escalated_Date", type date}, {"Prediction_Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Prediction before Escalation", each if [Prediction_Date]<[Escalated_Date] then 1 else 0,Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"serialnumber"}, {{"SN_PbE", each List.Max([Prediction before Escalation]), type number}, {"Rows", each _, type table [serialnumber=nullable text, Escalated_Date=nullable date, Prediction_Date=nullable date, Prediction before Escalation=number]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Escalated_Date", "Prediction_Date", "Prediction before Escalation"}, {"Escalated_Date", "Prediction_Date", "Prediction before Escalation"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"serialnumber", "Escalated_Date", "Prediction_Date", "Prediction before Escalation", "SN_PbE"})
in
    #"Reordered Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".