Forum Discussion
Condition to proactively predict escalation
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 |
1 Reply
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".
