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.
Hi,
So theoretically I know exactly what I want to do. I am simply having a difficult time executing that in Power Bi.
Basically I have a ton of data on helpdesk tickets.
What I want is the difference in closure times for conescutive tickets per agent per day.
I.E. How long did Agent A spend between closing ticket 1 versus ticket 2 versus ticket 3 versus ticket 4 on Septemeber 12th ect... ect...
The actual data has about 20 agents and over 75,000 tickets.
Solved! Go to Solution.
Hi @Tucker1991,
Please create some calculated columns with below formulas:
Index = CALCULATE ( COUNTROWS ( 'Agent ticket' ), FILTER ( ALL ( 'Agent ticket' ), 'Agent ticket'[Agent] = EARLIER ( 'Agent ticket'[Agent] ) && 'Agent ticket'[Date Closed].[Date] = EARLIER ( 'Agent ticket'[Date Closed].[Date] ) && 'Agent ticket'[Ticket ID] < EARLIER ( 'Agent ticket'[Ticket ID] ) ) ) + 1 Closed time for previous Ticket = CALCULATE ( MAX ( 'Agent ticket'[Date Closed] ), FILTER ( ALLEXCEPT ( 'Agent ticket', 'Agent ticket'[Agent], 'Agent ticket'[Date Closed].[Date] ), 'Agent ticket'[Index] = EARLIER ( 'Agent ticket'[Index] ) - 1 ) ) spend time = DATEDIFF ( 'Agent ticket'[Closed time for previous Ticket], 'Agent ticket'[Date Closed], SECOND )
Best regards,
Yuliana Gu
Hi @Tucker1991,
Please create some calculated columns with below formulas:
Index = CALCULATE ( COUNTROWS ( 'Agent ticket' ), FILTER ( ALL ( 'Agent ticket' ), 'Agent ticket'[Agent] = EARLIER ( 'Agent ticket'[Agent] ) && 'Agent ticket'[Date Closed].[Date] = EARLIER ( 'Agent ticket'[Date Closed].[Date] ) && 'Agent ticket'[Ticket ID] < EARLIER ( 'Agent ticket'[Ticket ID] ) ) ) + 1 Closed time for previous Ticket = CALCULATE ( MAX ( 'Agent ticket'[Date Closed] ), FILTER ( ALLEXCEPT ( 'Agent ticket', 'Agent ticket'[Agent], 'Agent ticket'[Date Closed].[Date] ), 'Agent ticket'[Index] = EARLIER ( 'Agent ticket'[Index] ) - 1 ) ) spend time = DATEDIFF ( 'Agent ticket'[Closed time for previous Ticket], 'Agent ticket'[Date Closed], SECOND )
Best regards,
Yuliana Gu
Would I basically want to use a DAX formula similar to the one below.
Column = DATEDIFF (
'Ticket Data'[Time],
CALCULATE (
MIN ( [Time] ),
FILTER ( ALL ( 'Ticket Data' ), [INDEX] < EARLIER ( 'Ticket Data'[INDEX] ) )
),
SECOND
)
But add IF Agent and Date is the same?
Thanks,
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |