Forum Discussion

jmarlatt's avatar
jmarlatt
New Member
9 years ago
Solved

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_Seamark's avatar
    Phil_Seamark
    Microsoft 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.

    • jmarlatt's avatar
      jmarlatt
      New Member

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

       

      IncidentNoActionIDActionDateTimeActionDescription
      INC20170221054243799832/22/17 6:57 AMIncident set to resolved
      INC20170222011443800102/22/17 7:03 AMIncident status set to open
      INC20170222011443800112/22/17 7:03 AMIncident assigned to department:  ---
      INC20170222011443800122/22/17 7:03 AMIncident assigned to: Doe, John
      INC20170221057343801872/22/17 7:47 AMIncident assigned to: Doe, John
      INC20170221064743802102/22/17 7:53 AMIncident status set to Unpaused
      INC20170216022143802182/22/17 7:55 AMIncident status set to Unpaused
      INC20170221064743802202/22/17 7:55 AMIncident unassigned
      INC20170216022143802242/22/17 7:57 AMIncident set to resolved
      INC20170221064743802282/22/17 7:57 AMIncident unassigned
      INC20170221064743802832/22/17 8:07 AMIncident assigned to: Doe, John
      INC20170221031343803652/22/17 8:16 AMIncident status set to Unpaused
      INC20170221031343803812/22/17 8:20 AMIncident unassigned
      INC20170222014243803852/22/17 8:21 AMIncident status set to open
      INC20170222014243803862/22/17 8:21 AMIncident assigned to department: Help Desk
      INC20170222014243803872/22/17 8:21 AMIncident assigned to: Doe, John
      INC20170221031343804012/22/17 8:22 AMIncident assigned to: Doe, John
      INC20170222014243804032/22/17 8:22 AMIncident ESCALATED from TRIAGE to TIER 3
      INC20170222014243804042/22/17 8:22 AMIncident assigned to department:  ---
      INC20170222014243804052/22/17 8:22 AMIncident assigned to: Doe, John
      INC20170222014743804182/22/17 8:25 AMIncident status set to open
      INC20170222014743804192/22/17 8:25 AMIncident assigned to department: Help Desk
      INC20170222014943804242/22/17 8:26 AMIncident status set to open
      INC20170222014943804252/22/17 8:26 AMIncident assigned to department: Help Desk
      INC20170222014943804262/22/17 8:26 AMIncident assigned to: Doe, John
      INC20170222014943804372/22/17 8:27 AMIncident set to resolved
      INC20170222014943804382/22/17 8:27 AMIncident ESCALATED from TRIAGE to TIER 1
      INC20170222014743804712/22/17 8:30 AMIncident ESCALATED from TRIAGE to TIER 1
      INC20170222014743804722/22/17 8:30 AMIncident assigned to: Doe, John
      INC20170222014743805182/22/17 8:36 AMIncident ESCALATED from TIER 1 to TIER 3
      INC20170222014743805192/22/17 8:36 AMIncident assigned to department:  ---
      INC20170222014743805202/22/17 8:36 AMIncident assigned to: Doe, John
      INC20170222015843805332/22/17 8:37 AMIncident status set to open
      INC20170222015843805342/22/17 8:37 AMIncident assigned to department: Help Desk
      INC20170221067443805852/22/17 8:45 AMIncident set to unresolved
      INC20170221064743805982/22/17 8:46 AMIncident set to resolved
      INC20170222015843806162/22/17 8:48 AMIncident ESCALATED from TRIAGE to TIER 1
      INC20170222015843806172/22/17 8:48 AMIncident assigned to: Doe, John
      INC20170221031343806432/22/17 8:51 AMIncident assigned to: Doe, John
      INC20170217051143806662/22/17 8:53 AMIncident set to resolved
      INC20170222017343807382/22/17 9:04 AMIncident status set to open
      INC20170222017343807392/22/17 9:04 AMIncident assigned to department: Help Desk
      INC20170222017343807732/22/17 9:09 AMIncident ESCALATED from TRIAGE to TIER 1
      INC20170222017343807742/22/17 9:09 AMIncident assigned to: Doe, John
      INC20170221031343808032/22/17 9:12 AMIncident set to resolved
      INC20170222017843808072/22/17 9:12 AMIncident status set to open
      INC20170222017843808082/22/17 9:12 AMIncident assigned to department: Help Desk
      INC20170222017843808132/22/17 9:13 AMIncident ESCALATED from TRIAGE to TIER 1
      INC20170222017843808142/22/17 9:13 AMIncident assigned to: Doe, John
      INC20170221024743808192/22/17 9:14 AMIncident status set to Unpaused
      INC20170221063843808272/22/17 9:15 AMIncident status set to Unpaused
      INC20170221024743808382/22/17 9:17 AMIncident set to resolved
      INC20170222017843809192/22/17 9:25 AMIncident set to resolved
      INC20170222019143809322/22/17 9:26 AMIncident status set to open
      INC20170222019143809332/22/17 9:26 AMIncident assigned to department: Help Desk
      • Sean's avatar
        Sean
        Community Champion

        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! :smileyhappy: