Forum Discussion
Find time between one fixed row and one variable row
Hello, I hope some can help me, i have a data table with this columns
- TicketID
- Ticket creation date
- Last action
- Date/time of last action
Actions can be things like giving a priority to the ticket or give a solution to the customer. For every action in a ticket a new table row is created.
I want to create something that shows me the average time between the giving a priority to a ticket action and the first action that follows on giving a priority to a ticket. The difficulty is that the action that follows on giving a priority to a ticket varies.
Can someone help me with how to get this done?
Hi Anonymous,
It looks like there existing more than one records where Action is set to "Set Priority to regular" per Ticket ID. If so, please try below suggestion.
First, add a calculated column in Table1.
Index = RANKX ( FILTER ( 'Table1', Table1[TicketID] = EARLIER ( Table1[TicketID] ) ), Table1[Action date/time], , ASC, DENSE )Then, add measure [Average] into a card visual.
Timediff = VAR _CurrentActiontime = SELECTEDVALUE ( Table1[Action date/time] ) VAR _NextActiontime = CALCULATE ( SELECTEDVALUE ( Table1[Action date/time] ), FILTER ( ALLEXCEPT ( Table1, Table1[TicketID] ), Table1[Index] = MAX ( Table1[Index] ) + 1 ) ) VAR diff = IF ( SELECTEDVALUE ( Table1[Action] ) = "Set priority to regular", DATEDIFF ( _CurrentActiontime, _NextActiontime, SECOND ), BLANK () ) RETURN diff
Average =
AVERAGEX ( ALLSELECTED ( Table1 ), [Timediff] )Best regards,
Yuliana Gu
13 Replies
Hi Anonymous
Can you post sample data or share the pbix? It'd be easier to help out like that
- Sure, this is the sample data. I hope this helps, thank you for your help.
TicketID Ticket created Action Action date/time 152111 7-1-2019 15:03:09 Ticket created 7-1-2019 15:03:09 152111 7-1-2019 15:03:09 Set priority to regular 7-1-2019 15:25:00 152111 7-1-2019 15:03:09 Give solution to customer 7-1-2019 16:01:03 152111 7-1-2019 15:03:09 Ticket closed 7-1-2019 16:02:01 Anonymous
What do you exactly mean by 'giving priority to a ticket'? Is this when 'Action' is set to "Set priority to regular"?
Do you want to see the time between when 'Action' is set to "Set priority to regular" and whatever comes immediately afterwards for a specific TicketID?
Providing an example with your sample data would probably help clarify
Hi Anonymous,
It looks like there existing more than one records where Action is set to "Set Priority to regular" per Ticket ID. If so, please try below suggestion.
First, add a calculated column in Table1.
Index = RANKX ( FILTER ( 'Table1', Table1[TicketID] = EARLIER ( Table1[TicketID] ) ), Table1[Action date/time], , ASC, DENSE )Then, add measure [Average] into a card visual.
Timediff = VAR _CurrentActiontime = SELECTEDVALUE ( Table1[Action date/time] ) VAR _NextActiontime = CALCULATE ( SELECTEDVALUE ( Table1[Action date/time] ), FILTER ( ALLEXCEPT ( Table1, Table1[TicketID] ), Table1[Index] = MAX ( Table1[Index] ) + 1 ) ) VAR diff = IF ( SELECTEDVALUE ( Table1[Action] ) = "Set priority to regular", DATEDIFF ( _CurrentActiontime, _NextActiontime, SECOND ), BLANK () ) RETURN diff
Average =
AVERAGEX ( ALLSELECTED ( Table1 ), [Timediff] )Best regards,
Yuliana Gu
v-yulgu-msft this does the trick! Thank you very much for your help!
AlB also many thanks for all your help!
