Email Response Time from email chain (email object in salesforce)

[rjs2]

Hi!  I have been trying to figure how to get response time from customer emails by our staff. 

The problem I am running into is that I cant figure out how to get the calculate time within an email chain with multiple responses.  Usinger 'earlier' just calculates it from the first two.  I do have my emails ranked.

I want to do this calculation in DAX and not in power query

This is what I have thats just giving me first incoming email and first outbound email:

ResponseTimeAll =

var cust_first_email_time = CALCULATE(MIN('Email Message'[MessageDate]),FILTER('Email Message','Email Message'[ParentId]=EARLIER('Email Message'[ParentId]) && 'Email Message'[Incoming]=TRUE()))

var ren_first_email_time = CALCULATE(MIN('Email Message'[MessageDate]),FILTER('Email Message','Email Message'[ParentId]=EARLIER('Email Message'[ParentId]) && 'Email Message'[Incoming]=FALSE()))

return DATEDIFF(cust_first_email_time,ren_first_email_time,minute)

[rjs2 · Accepted solution]

Ok, I am a bit closer now due to finding this:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-service-response-ti...

ResponseTimeAll =

SUMX (

    VALUES ('Email Message'[ParentId] ),

    CALCULATE (

        IF (

            MAX ( 'Email Message'[IncomingText])="TRUE",

            VAR X = MAX ( 'Email Message'[MessageDate] )

            VAR FilteredTable =

                FILTER (

                    CALCULATETABLE (

                        'Email Message',

                        ALLEXCEPT ( 'Email Message', 'Email Message'[ParentId] )

                    ),

                    'Email Message'[MessageDate] > X && 'Email Message'[IncomingText]="FALSE"

                )

            VAR Y = MINX ( FilteredTable, 'Email Message'[MessageDate]  )

            VAR TimeDuration = DATEDIFF ( X, Y, SECOND )

            VAR Result = DIVIDE ( TimeDuration, 60 )

            RETURN

                Result

        ))

)

I had to make some ammendments, but Its calculating on the incoming email the time til the next outgoing email.

We have auto response on, so I need to find away to mark those and have it skip the auto response