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. ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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)      



     )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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
9 years ago

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
    9 years ago

    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:

    • jmarlatt's avatar
      jmarlatt
      New Member
      9 years ago

      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.  

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      jmarlatt
      New Member
      9 years ago

      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...

       

      • jmarlatt's avatar
        jmarlatt
        New Member
        9 years ago

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