Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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)
)
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.
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 |
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)
)
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...
What does the error say when you hover over it?
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!!!!
Nice work.
I can add a Ranking column to the table too if you want and index of the incident actions per incident
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.
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
@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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |