Count the initial assignment date and initial reply of incident, the second, third, etc.

[Jack_Reacher]

Hello all, 

I'm struggling to find the right calculations in either Power query or Dax for the following problem:

I have a table that contains the details of incidents treated by level 3 technicians. These incidents can be assigned and reassigned based on the feedback received from customers since sometimes the technicians need additional information. The incidents are always intercepted by one specific person (let's call him Jack). Jack is the one who acts on behalf of the clients by providing their feedback to the level 3 technicians. 

what I want to accomplish here are three things: 

First find for each ticket the date of the initial, second, third, etc assignments by Jack (can be assistant of action and receiver of incident ticket),

Second is the first date of reply of the level 3 technician, then second, third reply by technician, etc...

Third is the time between the first assignment by Jack and first reply by technician, etc...

I've tried the minx and maxx calculations by specifying the conditions by it doesn't seem to work well for me

my humble attempt is below: 

First_assignment_date =

VAR _Name = MIN(WEEKLY_DATA[Incident numer])

RETURN

    MINX(

        FILTER(

            ALL(WEEKLY_DATA),

            WEEKLY_DATA[Incident numer] = _Name

        ),

        WEEKLY_DATA[DATE/HEURE ACTION]

    )

Second_assignment_date =

VAR _Name = MAX(WEEKLY_DATA[Incident numer])

RETURN

    MAXX(

        FILTER(

            ALL(WEEKLY_DATA),

            WEEKLY_DATA[DOSSIER_Nb] = _Name &&

            WEEKLY_DATA[ID_ASSISTANT] = SELECTEDVALUE(WEEKLY_DATA[ID_ASSISTANT])

        ),

        WEEKLY_DATA[DATE/HEURE ACTION]

    )

Duration = 

Duration__WD =

VAR MaxDate = [Max_date_WD]

VAR MinDate = [Min_Date_WD]

VAR DurationMinutes = (MaxDate - MinDate) * 1440  // Convert days to minutes

VAR Days = TRUNC(DurationMinutes / 1440)

VAR Hours = TRUNC((DurationMinutes - Days * 1440) / 60)

VAR Minutes = ROUND(DurationMinutes - Days * 1440 - Hours * 60, 0)  // Round to 0 decimal places

RETURN

    Days & " Jr | " & Hours & " Hr| " & Minutes & "m"

[dufoq3]

Hi @Jack_Reacher, I'd like to help you but I need to know:

1. which column is datetime of 1st initial assignment by Jack
2. which column is datetime of reply by level 3 technicians
3. in which column do we have technicians? Should I filter only level 3?
4. should I group IDs by Numéro de dossier?

Make you descripsion as detailed as possible. Provide also 2-3 examples of result please (it could be a screenshot).

[Jack_Reacher]

Hello @dufoq3 , thanks for the reply, actually all the dates both assignments of Jack and replies of technicians (and those of Jack) are found in one single column "Date de l'action journal" in raw data, and "date of action" in the BI report file. The column "Nombre de Réouverture du dossier" shouldn't be taken into account since it's not relevant. You may group them by Numero de dossier (incident number) if you want, please feel free to experiment. Thanks a lot

the main ones to consider are : 

Numéro de dossier (Incident number)

Date de l'action journal (date of the action on the incident)

Nom de l assisteur de l action (name of the assistant on the incidents

ID assisteur de l'action (ID of assistant)

ID Recepteur (ID of receiver of the incident)

Détail journal dans la langue de la plateforme (name of the receiver of the incident)

[dufoq3]

So let's talk about Incident No 158609043.

Could you provide expected result for this please?

Incident No	Date of Action	Assistant ID	Assistant Name	Receiver ID	Receiver Name
15869043	7. 3. 2023 17:03:39	E559369	SALAHDINE Hicham	2 - DOC	N2 DOC N2 - DOC
15869043	7. 3. 2023 9:26:23	E580333	LAMHARMECH Salma	E608421	Ikram AIT ELHA E608421
15869043	7. 3. 2023 10:15:56	E608421	AIT ELHA Ikram	E468947	NabilaLOTFI E468947
15869043	9. 3. 2023 14:08:18	E559369	SALAHDINE Hicham	E608421	Ikram AIT ELHA E608421
15869043	9. 3. 2023 14:05:53	U259994	ZAMMAR Sid Ahmed	E559369	HICHAMSALAHDINE E559369
15869043	8. 3. 2023 10:16:15	J522049	COTTENCEAU Christophe	E609397	NicolasKERVELLA E609397
15869043	9. 3. 2023 13:21:37	E609397	KERVELLA Nicolas	U259994	Sid AhmedZAMMAR U259994

[Jack_Reacher]

I know it could be a bit confusing, if you've reloaded the raw data, things might change. The only rows to consider to do the three calculations are the ones where the column "Nom de l'action" have the value N2 VERS N2. The person named Jack in the BI file is Salma... let me explain to you step by step for the following incidents numbers, from the raw data of course...

16031301 is assigned to the technician Mouaad by Salma on 26/04/2023 17:05:10. Then the technician Mouaad sends back the incident to Salma on 31/05/2023 17:09:00. This took five days. Salma can then either close the incident or send it back to the technician based on the clients' feedback.

18066104 on 27/03/2024 13:03:53, here the technician assigned it to himself, then he sent it back to Salma on 27/03/2024 13:53:19. Which took about an hour to provide a response. 

Ticket 15594185 was assigned to the technician Mouad on 30/01/2023 09:26:16, then the technician sends it back to the Salma on 31/01/2023 11:36:48, so the technician took to respond 1 day and some hours. 

[dufoq3]

It is clearer now 😉

Few more questions

   1.) what if they sent same incindent to Salma twice. Would you like to mark it as occurence1 and occurence2?

   2.) in some cases they haven't sent incident to Salma, shall I calculate time between 1st occurence which is 3.12.2023 11:00:55 and now?

   3.) there are cases where technician send incident to Salma first

I've created this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\a028311\OneDrive - Volvo Group\Downloads\PowerQueryForum\JackReacher\WEEKLY_DATA.xlsx"), true, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    FilteredRowsN2N2 = Table.SelectRows(#"Rapport 1_Sheet", each (Text.Trim([Nom de l Action Journal]) = "DE N2 VERS N2")),
    RenamedColumns = Table.RenameColumns(FilteredRowsN2N2,{{"Numéro de dossier", "Incident No"}, {"Date de l'action journal", "Date of Action"}, {"Nom de l assisteur de l action", "Assistant Name"}, {"ID assisteur de l'action", "Assistant ID"}, {"ID Recepteur", "Receiver ID"}, {"Détail journal dans la langue de la plateforme", "Receiver Name"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Incident No", Int64.Type}, {"Date of Action", type datetime}, {"Assistant ID", type text}, {"Assistant Name", type text}, {"Receiver ID", type text}, {"Receiver Name", type text}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType,{"Incident No", "Date of Action", "Assistant ID", "Assistant Name", "Receiver ID", "Receiver Name"}),
    GroupedRows = Table.Group(RemovedOtherColumns, {"Incident No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}, {"Detail", each 
        [ today = DateTime.FixedLocalNow(),
          min = List.Min([Date of Action]),
          max = List.Max([Date of Action]),
          count = Table.RowCount(_),
          salmaCount = List.Count(List.Select([Receiver ID], (x)=> x = "E580333")),
          a = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type),
          b = Table.FillUp(Table.AddColumn(a, "Check", (x)=> if x[Receiver ID] = "E580333" then x[Index] else null), {"Check"}),
          c = Table.Group(b, {"Check"}, {{"Dif", (x)=> (if Table.RowCount(x) = 1 and List.Max(x[Receiver ID]) <> "E580333" then today else List.Max(x[Date of Action])) - List.Min(x[Date of Action]), type duration}}),
          d = if count = 1 then {today - min} else
              if salmaCount = 0 then {max - min} else c[Dif]
        ][d], type table} }),
    Ad_FinalTable = Table.AddColumn(GroupedRows, "Final Table", each 
        [ a = Table.FromColumns({{[Incident No]}, [Detail], {[All]}}, type table[Incident No=Int64.Type, Duration=duration, Detail=table]),
          b = Table.FillDown(Table.AddIndexColumn(a, "Occurence", 1, 1, Int64.Type), {"Incident No", "Detail"})
        ][b], type table),
    CombinedFinalTable = Table.Combine(Ad_FinalTable[Final Table]),
    Ad_TotalDays = Table.AddColumn(CombinedFinalTable, "Total Days", each Number.Round(Duration.TotalDays([Duration]), 2), type number),
    Ad_TotalHours = Table.AddColumn(Ad_TotalDays, "Total Hours", each Number.Round(Duration.TotalHours([Duration]), 1), type number)
in
    Ad_TotalHours

[Jack_Reacher]

Sorry for the late reply, as I was collecting the right answers from the technicians for you,

So in the first question, the initial assignment date and reply are similar, so only one occurrence can be considered, here the backup of Salma also assigned the ticket on the same date to the same tech. 

As for the second and third questions, can we please mark them "not applicable" or disregard them altogether. The rule of thumb for this data is that the journey of incident should start by Salma (or her backup Chourouk) in the "assistant name" and the receiver should be a technician. Then the technician should send back the same incident (being an assistant) to Salma or her backup (first occurrence). Then Salma or her backup replies back (as assistant) to the technician (receiver) (second occurence), etc... 

I hope it's a bit more clear, thanks a lot

[dufoq3 · Accepted solution]

Hi, so I've consider as "Key Users" Salmu Lahmarmech (ID: E580333) and Chorouk Aboumejd (ID: E603167).

Just for sure: for Incident No 17499150 we calculate time between 1st row and last row in red rectangle. Correct?

Check this query. In [Detail] column you can find table with details. Let me know if this fits expected result:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Address\WEEKLY_DATA.xlsx"), true, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    FilteredRowsN2N2 = Table.SelectRows(#"Rapport 1_Sheet", each (Text.Trim([Nom de l Action Journal]) = "DE N2 VERS N2")),
    RenamedColumns = Table.RenameColumns(FilteredRowsN2N2,{{"Numéro de dossier", "Incident No"}, {"Date de l'action journal", "Date of Action"}, {"Nom de l assisteur de l action", "Assistant Name"}, {"ID assisteur de l'action", "Assistant ID"}, {"ID Recepteur", "Receiver ID"}, {"Détail journal dans la langue de la plateforme", "Receiver Name"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Incident No", Int64.Type}, {"Date of Action", type datetime}, {"Assistant ID", type text}, {"Assistant Name", type text}, {"Receiver ID", type text}, {"Receiver Name", type text}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType,{"Incident No", "Date of Action", "Assistant ID", "Assistant Name", "Receiver ID", "Receiver Name"}),
    GroupedRows = Table.Group(RemovedOtherColumns, {"Incident No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}, {"Detail", each 
        [ keyUsersId = List.Buffer({"E580333", "E603167"}),
          a = Table.AddIndexColumn(Table.Distinct(_, {"Date of Action", "Assistant ID"}), "Index", 1, 1, Int64.Type),
          b = Table.FillUp(Table.AddColumn(a, "Check", (x)=> if List.Contains(keyUsersId, x[Receiver ID]) then x[Index] else null), {"Check"}),
          c = Table.SelectRows(b, (x)=> x[Check] <> null),
          d = Table.Group(c, {"Check"}, {{"Dif", (x)=> if Table.RowCount(x) <= 1 then null else List.Max(x[Date of Action]) - List.Min(x[Date of Action]), type duration}}),
          e = Table.SelectRows(d, (x)=> x[Dif] <> null)[Dif]
        ][e], type table} }),
    Ad_FinalTable = Table.AddColumn(GroupedRows, "Final Table", each 
        [ a = Table.FromColumns({{[Incident No]}, [Detail], {[All]}}, type table[Incident No=Int64.Type, Duration=duration, Detail=table]),
          b = Table.FillDown(Table.AddIndexColumn(a, "Occurence", 1, 1, Int64.Type), {"Incident No", "Detail"})
        ][b], type table),
    CombinedFinalTable = Table.Combine(Ad_FinalTable[Final Table]),
    Ad_TotalDays = Table.AddColumn(CombinedFinalTable, "Total Days", each Number.Round(Duration.TotalDays([Duration]), 2), type number),
    Ad_TotalHours = Table.AddColumn(Ad_TotalDays, "Total Hours", each Number.Round(Duration.TotalHours([Duration]), 1), type number)
in
    Ad_TotalHours

[Jack_Reacher]

I went ahead and tested it further, now it works fine, just now trying to figure out how to display the right action date for each ticket, thanks a lot for your support, you rock! 👏😊

[dufoq3]

Check this.

Result:

let
    Source = Excel.Workbook(File.Contents("C:\Users\a028311\OneDrive - Volvo Group\Downloads\PowerQueryForum\JackReacher\WEEKLY_DATA.xlsx"), true, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    FilteredRowsN2N2 = Table.SelectRows(#"Rapport 1_Sheet", each (Text.Trim([Nom de l Action Journal]) = "DE N2 VERS N2")),
    RenamedColumns = Table.RenameColumns(FilteredRowsN2N2,{{"Numéro de dossier", "Incident No"}, {"Date de l'action journal", "Date of Action"}, {"Nom de l assisteur de l action", "Assistant Name"}, {"ID assisteur de l'action", "Assistant ID"}, {"ID Recepteur", "Receiver ID"}, {"Détail journal dans la langue de la plateforme", "Receiver Name"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Incident No", Int64.Type}, {"Date of Action", type datetime}, {"Assistant ID", type text}, {"Assistant Name", type text}, {"Receiver ID", type text}, {"Receiver Name", type text}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType,{"Incident No", "Date of Action", "Assistant ID", "Assistant Name", "Receiver ID", "Receiver Name"}),
    GroupedRows = Table.Group(RemovedOtherColumns, {"Incident No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}, {"Detail", each 
        [ keyUsersId = List.Buffer({"E580333", "E603167"}),
          a = Table.AddIndexColumn(Table.Distinct(_, {"Date of Action", "Assistant ID"}), "Index", 1, 1, Int64.Type),
          b = Table.FillUp(Table.AddColumn(a, "Check", (x)=> if List.Contains(keyUsersId, x[Receiver ID]) then x[Index] else null), {"Check"}),
          c = Table.SelectRows(b, (x)=> x[Check] <> null),
          d = Table.Group(c, {"Check"}, {{"Result", (x)=> if Table.RowCount(x) <= 1 then null else [Assign Date = List.Min(x[Date of Action]), Reply Date = List.Max(x[Date of Action]), Dif = #"Reply Date" - #"Assign Date"]}}),
          e = try Table.FromRecords(List.Select(d[Result], (x)=> x <> null)) otherwise null
        ][e], type table} }),
    Ad_FinalTable = Table.AddColumn(GroupedRows, "Final Table", each 
        [ a = Table.FromColumns({{[Incident No]}} & (if [Detail] = null or Table.IsEmpty([Detail]) then Table.ToColumns(#table(null, {List.Repeat({null}, 3)})) else Table.ToColumns([Detail])) & {{[All]}}, type table[Incident No=Int64.Type, Assign Date=datetime, Reply Date=datetime, Duration=duration, Detail=table]),
          b = Table.FillDown(Table.AddIndexColumn(a, "Occurence", 1, 1, Int64.Type), {"Incident No", "Detail"})
        ][b], type table),
    CombinedFinalTable = Table.Combine(Ad_FinalTable[Final Table]),
    Ad_TotalDays = Table.AddColumn(CombinedFinalTable, "Total Days", each Number.Round(Duration.TotalDays([Duration]), 2), type number),
    Ad_TotalHours = Table.AddColumn(Ad_TotalDays, "Total Hours", each Number.Round(Duration.TotalHours([Duration]), 1), type number)
in
    Ad_TotalHours

[Jack_Reacher]

Yes for the rectangle, we calculate the time between first row (but it has to have Salma or Chorouk mentioned as assistant) and last row (has to mention one of the techs in assistant column, and Salma or Chorouk in receiver).

Can you please also include the dates of first assignment, first reply, second assignment, second reply, etc ? since it's one of the requirements, and it would allow me to easily test and compare if the calculations are accurate

Best regards,

Jack

[dufoq3]

Provide expected result for screeenshot above please.

[Jack_Reacher]

Yes for sure, in addition to the duration column, here is somewhat the expected result should look like:
from two tables of the same weekly_data, I included an index column to keep duplicates, I extracted the min action dates and filtered where only the assistant is Salma or Chorouk to get the initial assignment date, then I duplicated the column to get the initial reply dates, where the assistant name include all technicians except for Salma or Chorouk, then I merged the two tables to get the following;