Forum Discussion
Copy value from nearby row
I have a table with call events. Unfortunately are only the speak duration "linked" with the queue but I'm also interested in measurements like average speak time for each queue.
A call can have none, one, or multiple queue and conversation events. If there is a conversation, is it always the next sequence after the queue (it happens that callers disconnect before the conversation).
I want to be able to calculate that the info queue has 180 sec speak time and the Sale queue has a 120 sec speak time.
A very simplified version of the table looks like this. There are a call_id and call_sequence_nr for each call.
| Call_id | Call_sequence_nr | Event | Queue | Duration_sec |
| 1 | 1 | Menu | ||
| 1 | 2 | Queue | Info | 20 |
| 1 | 3 | Conversation | 180 | |
| 1 | 4 | Queue | Sale | 40 |
| 1 | 5 | Conversation | 120 |
1 Reply
ryan_mayuSuper User
Anonymous
is this what you want?
Column = VAR _sec=maxx(FILTER('Table','Table'[Call_id]=EARLIER('Table'[Call_id])&&'Table'[Call_sequence_nr]=EARLIER('Table'[Call_sequence_nr])+1),'Table'[Duration_sec]) return if('Table'[Queue]<>"",_sec)