Need help with Dax and data

[DiKi-I]

Hi all,

I have history data of tickets and I want to calculate two kpis one is how many tickets was fixed in first contact and another is the average time to acknowledge the ticket when it was created.
Attached is the sample data. When a ticket arrives it gets assigned so the first record of the ticket will be used to find the average time to acknowledge the ticket and if a ticket has only one assignee/one record those will be first time fix.
Can someone help me with the dax. This data will only have resolved tickets history.

https://docs.google.com/spreadsheets/d/1BLONNk8-VkhHD5WGsFHchAKmfSdP7hNN/edit?usp=drive_link&ouid=10...

[DiKi-I]

But same ticket can have same assignee multiple time so that it should not be counted as unique.
The uniquness will be then on assignee and start time

[adudani]

Try the updated code below.

Tickets:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9IDRXMLQyMMDFM4LxjOC8WB2ICSRowW6AMxYTsOgxxm6CEVDMCSGOagI+81BMcAGLG6PoMUHRg4sHMsEYKOaKEEc1gaB5sbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TICKET NO" = _t, ASSIGNEE = _t, #"CREATION DATE" = _t, #"START DATE" = _t, #"END DATE" = _t, #"resolve date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET NO", Int64.Type}, {"ASSIGNEE", type text}, {"CREATION DATE", type datetime}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"resolve date", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TICKET NO"}, CountofAssignees, {"TICKET NO"}, "CountofAssignees", JoinKind.LeftOuter),
    #"Expanded CountofAssignees" = Table.ExpandTableColumn(#"Merged Queries", "CountofAssignees", {"Count of Number of Assignees"}, {"Count of Number of Assignees"}),
    #"Fixed in First Contact Flag" = Table.AddColumn(#"Expanded CountofAssignees", "Fixed in First Contact Flag", each if [Count of Number of Assignees] =1 and [resolve date]<> null then "Fixed in First Contact" else "Not Fixed in First Contact", type text)
in
    #"Fixed in First Contact Flag"

CountofAssignees:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9IDRXMLQyMMDFM4LxjOC8WB2ICSRowW6AMxYTsOgxxm6CEVDMCSGOagI+81BMcAGLG6PoMUHRg4sHMsEYKOaKEEc1gaB5sbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TICKET NO" = _t, ASSIGNEE = _t, #"CREATION DATE" = _t, #"START DATE" = _t, #"END DATE" = _t, #"resolve date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET NO", Int64.Type}, {"ASSIGNEE", type text}, {"CREATION DATE", type datetime}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"resolve date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Unique Assignee", each Text.Combine({[ASSIGNEE],Text.From(Number.From([START DATE]))},"-")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"TICKET NO"}, {{"Data", each _, type table [TICKET NO=nullable number, ASSIGNEE=nullable text, CREATION DATE=nullable datetime, START DATE=nullable datetime, END DATE=nullable datetime, resolve date=nullable datetime, Unique Assignee=text]}}),
    #"Unique Assignee" = Table.AddColumn(#"Grouped Rows", "Count of Number of Assignees", each List.Count(List.Distinct([Data][Unique Assignee]))),
    #"Total Time Between Max Start DateTime and Min Creation DateTime" = Table.AddColumn(#"Unique Assignee", "Total Time Between Max Start DateTime and Min Creation DateTime", each List.Max([Data][START DATE])- List.Min([Data][CREATION DATE])),
    #"Avg. Time to Acknowledge the Ticket" = Table.AddColumn(#"Total Time Between Max Start DateTime and Min Creation DateTime", "Avg. Time to Acknowledge the Ticket", each [Total Time Between Max Start DateTime and Min Creation DateTime]/[Count of Number of Assignees]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Avg. Time to Acknowledge the Ticket",{{"Total Time Between Max Start DateTime and Min Creation DateTime", type duration}, {"Avg. Time to Acknowledge the Ticket", type duration}, {"Count of Number of Assignees", Int64.Type}})
in
    #"Changed Type1"

In the CountofAssignees table:

-The grouping is based on Ticket number. A unique assignee which is a combination of Assignee and Start Time is used for the count. So, I think if it's assigned back to the same person at a later start date, it is counted.

- To get the Total time: took the max start date in the list - min creation date in the list for the grouped ticket number table i.e. [Data].

- For the average, divided the total by the count of number of assignees.

Let me know if this helps.

- Reason why I prefer power query over calculated dax column is because, M is easily portable rather than recreating the column / dax logic.

[DiKi-I]

Also I was able to do this as a calculate column using below dax:

AssigneeCountPerTicket =

CALCULATE(

    COUNTROWS(VALUES('Assignee History1')),

    ALLEXCEPT('Assignee History1', 'Assignee History1'[incident number])

)

[DiKi-I]

1. I followed the steps and i am able to get the ticket which are resolved in first contact.
2. avg. duration to acknowledge the ticket - this will be the difference of creation and start date , I also have to consider the ticket which are not resolved in first attempt. This will be the first record of any ticket which has arrived sorted by start date. Let me know if its clear. Basically what I think is to rank all the incidents based on start time, and calculate the difference of time which has rank 1.

[DiKi-I]

Thank you so much. I will check your code and will get back to you.

[adudani]

hi @DiKi-I ,

open up the power query editor and copy and paste in the code below:

tickets table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9IDRXMLQyMMDFM4LxjOC8WB2ICSRowW6AMxYTsOgxxm6CEVDMCSGOagI+81BMcAGLG6PoMUHRg4sHMsEYKOaKEEc1gaB5sbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TICKET NO" = _t, ASSIGNEE = _t, #"CREATION DATE" = _t, #"START DATE" = _t, #"END DATE" = _t, #"resolve date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET NO", Int64.Type}, {"ASSIGNEE", type text}, {"CREATION DATE", type datetime}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"resolve date", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TICKET NO"}, CountofAssignees, {"TICKET NO"}, "CountofAssignees", JoinKind.LeftOuter),
    #"Expanded CountofAssignees" = Table.ExpandTableColumn(#"Merged Queries", "CountofAssignees", {"Count of Number of Assignees"}, {"Count of Number of Assignees"}),
    #"Fixed in First Contact Flag" = Table.AddColumn(#"Expanded CountofAssignees", "Fixed in First Contact Flag", each if [Count of Number of Assignees] =1 and [resolve date]<> null then "Fixed in First Contact" else "Not Fixed in First Contact", type text),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Fixed in First Contact Flag", "Subtraction", each if [Count of Number of Assignees] =1 then [START DATE] - [CREATION DATE] else null, type duration)
in
    #"Inserted Time Subtraction"

count of assignees:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9IDRXMLQyMMDFM4LxjOC8WB2ICSRowW6AMxYTsOgxxm6CEVDMCSGOagI+81BMcAGLG6PoMUHRg4sHMsEYKOaKEEc1gaB5sbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"TICKET NO" = _t, ASSIGNEE = _t, #"CREATION DATE" = _t, #"START DATE" = _t, #"END DATE" = _t, #"resolve date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET NO", Int64.Type}, {"ASSIGNEE", type text}, {"CREATION DATE", type datetime}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"resolve date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TICKET NO"}, {{"Count of Number of Assignees", each List.Count(List.Distinct([ASSIGNEE])),type number}})
in
    #"Grouped Rows"

 let me know if this resolves: 

1. Resolved in first contact or not

2. avg. duration to acknowledge the ticket if it is solved in first contact ( this is based on start date - creation date) .

I will work on the logic for avg. duration for not solved in first contact if the above resolves your issues.