Find the minimum value within grouped data

[jmarlatt]

Hello,

I need to replication a query that is done in access on a table within Powerbi. Im new to powerbi as well. 

So a little background first, i am reporting on a Help Desk ticking system. This system is home grown and only have access to certian sub-sets of records. 

1 of these tables is "StaffActions" basically a record of every single action that occurs on a help desk ticket, for all tickets that were actioned on. 

For every IncidentNo, there are multiple unique ActionID. These ActionID has an associated Action Description, ActionDateTime. 

I need to filter only actions that the ActionDescription has "Incident assigned to:*" (Notice the wild card) 

Now most incidents eventually have multiple "Incidents assigned to:*" records. Since they get escalated and bounced around to different engineers. 

I then need to find the ActionDateTime of the earliest record for each IncidentNo in the table. 

I can do it in Access using the below query:

I spent several hours trying to figure out how to do this in a calculated column, or calcutlated table and no luck. 

End result is I will compare the "FristActionDateTime" for each ticket to the tickets OpenDateTime to find out how long each time waited in the queue before its first assignment. 

Any ideas, is this even possible with PowerBi? 

[Phil_Seamark]

Hi @jmarlatt,

Any chance you can provide a small sampe set of data?  I reckon we can come up with something pretty quickly for you if you do.

[jmarlatt]

Thank you! Here is some data. Sanitized but still relevent.

IncidentNo	ActionID	ActionDateTime	ActionDescription
INC201702210542	4379983	2/22/17 6:57 AM	Incident set to resolved
INC201702220114	4380010	2/22/17 7:03 AM	Incident status set to open
INC201702220114	4380011	2/22/17 7:03 AM	Incident assigned to department:  ---
INC201702220114	4380012	2/22/17 7:03 AM	Incident assigned to: Doe, John
INC201702210573	4380187	2/22/17 7:47 AM	Incident assigned to: Doe, John
INC201702210647	4380210	2/22/17 7:53 AM	Incident status set to Unpaused
INC201702160221	4380218	2/22/17 7:55 AM	Incident status set to Unpaused
INC201702210647	4380220	2/22/17 7:55 AM	Incident unassigned
INC201702160221	4380224	2/22/17 7:57 AM	Incident set to resolved
INC201702210647	4380228	2/22/17 7:57 AM	Incident unassigned
INC201702210647	4380283	2/22/17 8:07 AM	Incident assigned to: Doe, John
INC201702210313	4380365	2/22/17 8:16 AM	Incident status set to Unpaused
INC201702210313	4380381	2/22/17 8:20 AM	Incident unassigned
INC201702220142	4380385	2/22/17 8:21 AM	Incident status set to open
INC201702220142	4380386	2/22/17 8:21 AM	Incident assigned to department: Help Desk
INC201702220142	4380387	2/22/17 8:21 AM	Incident assigned to: Doe, John
INC201702210313	4380401	2/22/17 8:22 AM	Incident assigned to: Doe, John
INC201702220142	4380403	2/22/17 8:22 AM	Incident ESCALATED from TRIAGE to TIER 3
INC201702220142	4380404	2/22/17 8:22 AM	Incident assigned to department:  ---
INC201702220142	4380405	2/22/17 8:22 AM	Incident assigned to: Doe, John
INC201702220147	4380418	2/22/17 8:25 AM	Incident status set to open
INC201702220147	4380419	2/22/17 8:25 AM	Incident assigned to department: Help Desk
INC201702220149	4380424	2/22/17 8:26 AM	Incident status set to open
INC201702220149	4380425	2/22/17 8:26 AM	Incident assigned to department: Help Desk
INC201702220149	4380426	2/22/17 8:26 AM	Incident assigned to: Doe, John
INC201702220149	4380437	2/22/17 8:27 AM	Incident set to resolved
INC201702220149	4380438	2/22/17 8:27 AM	Incident ESCALATED from TRIAGE to TIER 1
INC201702220147	4380471	2/22/17 8:30 AM	Incident ESCALATED from TRIAGE to TIER 1
INC201702220147	4380472	2/22/17 8:30 AM	Incident assigned to: Doe, John
INC201702220147	4380518	2/22/17 8:36 AM	Incident ESCALATED from TIER 1 to TIER 3
INC201702220147	4380519	2/22/17 8:36 AM	Incident assigned to department:  ---
INC201702220147	4380520	2/22/17 8:36 AM	Incident assigned to: Doe, John
INC201702220158	4380533	2/22/17 8:37 AM	Incident status set to open
INC201702220158	4380534	2/22/17 8:37 AM	Incident assigned to department: Help Desk
INC201702210674	4380585	2/22/17 8:45 AM	Incident set to unresolved
INC201702210647	4380598	2/22/17 8:46 AM	Incident set to resolved
INC201702220158	4380616	2/22/17 8:48 AM	Incident ESCALATED from TRIAGE to TIER 1
INC201702220158	4380617	2/22/17 8:48 AM	Incident assigned to: Doe, John
INC201702210313	4380643	2/22/17 8:51 AM	Incident assigned to: Doe, John
INC201702170511	4380666	2/22/17 8:53 AM	Incident set to resolved
INC201702220173	4380738	2/22/17 9:04 AM	Incident status set to open
INC201702220173	4380739	2/22/17 9:04 AM	Incident assigned to department: Help Desk
INC201702220173	4380773	2/22/17 9:09 AM	Incident ESCALATED from TRIAGE to TIER 1
INC201702220173	4380774	2/22/17 9:09 AM	Incident assigned to: Doe, John
INC201702210313	4380803	2/22/17 9:12 AM	Incident set to resolved
INC201702220178	4380807	2/22/17 9:12 AM	Incident status set to open
INC201702220178	4380808	2/22/17 9:12 AM	Incident assigned to department: Help Desk
INC201702220178	4380813	2/22/17 9:13 AM	Incident ESCALATED from TRIAGE to TIER 1
INC201702220178	4380814	2/22/17 9:13 AM	Incident assigned to: Doe, John
INC201702210247	4380819	2/22/17 9:14 AM	Incident status set to Unpaused
INC201702210638	4380827	2/22/17 9:15 AM	Incident status set to Unpaused
INC201702210247	4380838	2/22/17 9:17 AM	Incident set to resolved
INC201702220178	4380919	2/22/17 9:25 AM	Incident set to resolved
INC201702220191	4380932	2/22/17 9:26 AM	Incident status set to open
INC201702220191	4380933	2/22/17 9:26 AM	Incident assigned to department: Help Desk

[Phil_Seamark · Accepted solution]

I don't think I have this 100% right but we might be close.

This returns a table of incidents showing the duration between the first datetime of the incident and the duration of each step in minutes for each step for each subsequent step.

Table = 

VAR T1 = SUMMARIZE(
             FILTER(
                 'Incidents',
                 LEFT([ActionDescription],20)="Incident assigned to"
                 ),
                 Incidents[IncidentNo],
                 "First Assigned",MIN('Incidents'[ActionDateTime])
                 )
                 
                 
RETURN ADDCOLUMNS(
            NATURALINNERJOIN('Incidents',T1),
            "Duration",IFERROR(DATEDIFF([First Assigned],'Incidents'[ActionDateTime],MINUTE) ,0)      



 )

[jmarlatt]

OK, its getting there.... Need to understand all this so breaking it down. I did the following to see what I would get in the table and error check.. but getting a column with #Error...

[Phil_Seamark]

What does the error say when you hover over it?

[jmarlatt]

Ha, it was a calculated column that got left in there that I was playing with earlier! the code does not generate any error. Once I validate results, adding the rest of the code to get the time difference... thank you!!!!

[Phil_Seamark]

Nice work. 

I can add a Ranking column to the table too if you want and index of the incident actions per incident

[jmarlatt]

That sounds intresting! It would be great to see that!  Also In additon to the text "Incident assigned to:" the actiondescription could also read "Incident acknowledged" which ever would come first is the time stamp i need to use.  

[v-huizhn-msft]

Hi @jmarlatt,

Have you resolved your issue? If you have resolved it, please mark the right reply as answer, so other people will find solution clearly. If you have not resolve the problem, please feel free to ask.

Best Regards,
Angelia

[Sean]

@jmarlattThe steps and image have been updated!

In the Query Editor

1) Add a Conditional Column and then filter the results as shown below

2) then Group By Incident Number by Min ActionDateTime

Hope this helps!

[jmarlatt]

Im also using the StaffAction table for other infromation. I dont see a way to use a seperate query thats not using data from the source.