Forum Discussion
Find the minimum value within grouped data
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?
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)
)
11 Replies
- Phil_SeamarkMicrosoft Employee
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.
- jmarlattNew Member
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