Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate 'service response time' per conversation id

Hi,

 

I would like to calculate the average 'service response time' per conversation id as a measure.

 

The 'service response time' is calculated by the difference between the 'created_at' variable from Y and X in minutes:

 

X = the first row where owner_type = "User" and is_interaction = 1.

Y = the first row after X where owner_type = "Agent" and owner_id <> 1

 

idowner_typeowner_idconversation_idmessagecreated_atis_interaction
260943Agent126276a2022-03-01 15:00:14 
265544Agent126276b2022-03-05 12:01:35 
266749User15326326276c2022-03-05 15:49:121
266750User15326326276d2022-03-05 15:49:221
266753Agent1426276e2022-03-05 15:51:09 
267003Agent126276f2022-03-06 12:01:23 
268904Agent126276g2022-03-07 12:00:53 
271141Agent126276h2022-03-09 12:00:55 
271725User15326326276i2022-03-09 13:01:071
271728User15326326276j2022-03-09 13:01:221
271727Agent1026276k2022-03-09 13:01:23 
272085Agent126276l2022-03-10 12:01:26 

 

Any ideas on how to calculate this?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Anonymous 

Here is the sample file with the solution https://www.dropbox.com/t/nRZI3zMA3aT7srRG
Following your latest clarification, aggregation by date (for example latest time per date) shall be required as part of the calculation. In order to achieve this without further complications in the code, a date column must be added.

Date = 
DATE ( 
    YEAR ( 'Service  Data'[created_at] ), 
    MONTH ( 'Service  Data'[created_at] ), 
    DAY ( 'Service  Data'[created_at] ) 
)

Then our master measure would be

Response Time (minutes) = 
VAR CurrentDateTime =
    MAX ( 'Service  Data'[created_at] )
VAR CurrentDate =
    MAX ( 'Service  Data'[Date] )
VAR CurrentType =
    MAX ( 'Service  Data'[owner_type] )
VAR CurrentConversationTable =
    CALCULATETABLE (
        'Service  Data',
        ALLEXCEPT ( 'Service  Data', 'Service  Data'[conversation_id] )
    )
VAR CurrentDateTypeTable =
    FILTER (
        CurrentConversationTable,
        'Service  Data'[Date] = CurrentDate
            && 'Service  Data'[owner_type] = CurrentType
    )
VAR NextTime_DateTypeTable =
    FILTER ( CurrentDateTypeTable, 'Service  Data'[created_at] > CurrentDateTime )
VAR NextTime_AgentTable =
    FILTER (
        CurrentConversationTable,
        'Service  Data'[created_at] > CurrentDateTime
            && 'Service  Data'[owner_type] = "Agent"
            && 'Service  Data'[owner_id] <> 1
    )
VAR X = CurrentDateTime
VAR Y =
    MINX ( NextTime_AgentTable, 'Service  Data'[created_at] )
VAR TimeDuration =
    DATEDIFF ( X, Y, SECOND )
VAR Result =
    DIVIDE ( TimeDuration, 60 )
RETURN
    IF (
        CurrentType = "User",
        IF ( ISBLANK ( COUNTROWS ( NextTime_DateTypeTable ) ), Result )
    )

Then depending on average of sum:

Total Response Time (minutes) = 
SUMX (
    VALUES ( 'Service  Data'[id] ),
    [Response Time (minutes)]
)
Average Response Time (minutes) = 
AVERAGEX (
    VALUES ( 'Service  Data'[id] ),
    [Response Time (minutes)]
)

1.png

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @Anonymous 

Here is the sample file with the solution https://www.dropbox.com/t/nRZI3zMA3aT7srRG
Following your latest clarification, aggregation by date (for example latest time per date) shall be required as part of the calculation. In order to achieve this without further complications in the code, a date column must be added.

Date = 
DATE ( 
    YEAR ( 'Service  Data'[created_at] ), 
    MONTH ( 'Service  Data'[created_at] ), 
    DAY ( 'Service  Data'[created_at] ) 
)

Then our master measure would be

Response Time (minutes) = 
VAR CurrentDateTime =
    MAX ( 'Service  Data'[created_at] )
VAR CurrentDate =
    MAX ( 'Service  Data'[Date] )
VAR CurrentType =
    MAX ( 'Service  Data'[owner_type] )
VAR CurrentConversationTable =
    CALCULATETABLE (
        'Service  Data',
        ALLEXCEPT ( 'Service  Data', 'Service  Data'[conversation_id] )
    )
VAR CurrentDateTypeTable =
    FILTER (
        CurrentConversationTable,
        'Service  Data'[Date] = CurrentDate
            && 'Service  Data'[owner_type] = CurrentType
    )
VAR NextTime_DateTypeTable =
    FILTER ( CurrentDateTypeTable, 'Service  Data'[created_at] > CurrentDateTime )
VAR NextTime_AgentTable =
    FILTER (
        CurrentConversationTable,
        'Service  Data'[created_at] > CurrentDateTime
            && 'Service  Data'[owner_type] = "Agent"
            && 'Service  Data'[owner_id] <> 1
    )
VAR X = CurrentDateTime
VAR Y =
    MINX ( NextTime_AgentTable, 'Service  Data'[created_at] )
VAR TimeDuration =
    DATEDIFF ( X, Y, SECOND )
VAR Result =
    DIVIDE ( TimeDuration, 60 )
RETURN
    IF (
        CurrentType = "User",
        IF ( ISBLANK ( COUNTROWS ( NextTime_DateTypeTable ) ), Result )
    )

Then depending on average of sum:

Total Response Time (minutes) = 
SUMX (
    VALUES ( 'Service  Data'[id] ),
    [Response Time (minutes)]
)
Average Response Time (minutes) = 
AVERAGEX (
    VALUES ( 'Service  Data'[id] ),
    [Response Time (minutes)]
)

1.png

Anonymous
Not applicable

True champ!

 

I aggregated the date with a 5 minutes timestamp.

 

This is the solution! Many thanks for the effort!

 

DATE = DATEVALUE ( 'Service Data'[created_at] )
    + TIME ( HOUR ( 'Service Data'[created_at] ), MINUTE ( 'Service Data'[created_at] )
        - MOD (
            MINUTE ( 'Service Data'[created_at] ),
            5
        ), 0 )

 

Good job 👍 

tamerj1
Super User
Super User

Hi @Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/eefOsChPez2kmESO

Based on your description and the provided sample data, the time of both message "c" & message "d" will be subtracted from the time of message "e".

Similarly the time of both message "i" & message "j" will be subtracted from the time of message "k".

Average service response time (minutes) = 
AVERAGEX ( 
    VALUES ('Service  Data'[id] ),
    CALCULATE ( 
        IF ( 
            MAX ( 'Service  Data'[owner_type] ) = "User",
            VAR X = MAX ( 'Service  Data'[created_at] )
            VAR FilteredTable = 
                FILTER ( 
                    CALCULATETABLE ( 
                        'Service  Data', 
                        ALLEXCEPT ( 'Service  Data', 'Service  Data'[conversation_id] ) 
                    ), 
                    'Service  Data'[created_at] > X && 'Service  Data'[owner_type] = "Agent" && 'Service  Data'[owner_id] <> 1
                )
            VAR Y = MINX ( FilteredTable, 'Service  Data'[created_at] )
            VAR TimeDuration = DATEDIFF ( X, Y, SECOND )
            VAR Result = DIVIDE ( TimeDuration, 60 )
            RETURN
                Result
        )
    )
)
Total service response time (minutes) = 
SUMX ( 
    VALUES ('Service  Data'[id] ),
    CALCULATE ( 
        IF ( 
            MAX ( 'Service  Data'[owner_type] ) = "User",
            VAR X = MAX ( 'Service  Data'[created_at] )
            VAR FilteredTable = 
                FILTER ( 
                    CALCULATETABLE ( 
                        'Service  Data', 
                        ALLEXCEPT ( 'Service  Data', 'Service  Data'[conversation_id] ) 
                    ), 
                    'Service  Data'[created_at] > X && 'Service  Data'[owner_type] = "Agent" && 'Service  Data'[owner_id] <> 1
                )
            VAR Y = MINX ( FilteredTable, 'Service  Data'[created_at] )
            VAR TimeDuration = DATEDIFF ( X, Y, SECOND )
            VAR Result = DIVIDE ( TimeDuration, 60 )
            RETURN
                Result
        )
    )
)

1.png

Anonymous
Not applicable

Hey @tamerj1 ,

 

Many thanks for helping me out! Getting really close now... I only want the calculate the 'service response time' of the latest row where owner_type = "User".

So the rows with 'id' 266749 and 271725 must be blank.

 

Any ideas on how to fix this? I think I should add an index column combined with the earlier function for this, but I would like to hear your approach 🙂

@Anonymous 

Yea can be done without index column but not today 😅

will get back to you tomorrow 

Anonymous
Not applicable

Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me

It is indeed

Anonymous
Not applicable

Awesome, I was trying to find a solution but I'm stuck. It's a tough one for me

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors