cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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:

 

2017-02-24_13-17-45.png

 

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? 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Find the minimum value within grouped data

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)      



)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
Highlighted
Microsoft
Microsoft

Re: Find the minimum value within grouped data

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
New Member

Re: Find the minimum value within grouped data

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
Highlighted
Community Champion
Community Champion

Re: Find the minimum value within grouped data

@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

 

QE - Keep Only Assigned To.gif

 

Hope this helps! Smiley Happy

Highlighted
Microsoft
Microsoft

Re: Find the minimum value within grouped data

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)      



)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
New Member

Re: Find the minimum value within grouped data

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.  

Highlighted
New Member

Re: Find the minimum value within grouped data

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

 

2017-02-24_15-00-15.png

Highlighted
New Member

Re: Find the minimum value within grouped data

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

Highlighted
Microsoft
Microsoft

Re: Find the minimum value within grouped data

What does the error say when you hover over it?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Microsoft
Microsoft

Re: Find the minimum value within grouped data

Nice work. 

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors