Initial Email Response from Exchange Connection

[danielgajohnson]

Hi everyone. I am at an organization that's interested in finding out which day of the week people are most likely to respond to our initial outreach emails. We use Office for emails, so the Exchange connector is pretty straightforward. I've also gotten as far as figuring out how to pull out the pertinent information to match received emails to sent emails, but that's as far as I've got. As a crude example, I've got a table that looks like this:

Folder Path	Subject	Sender	Recipient	DateTimeSent	DateTimeReceived	InternetMessageID	InReplyTo	Count of InternetMessageID
Inbox	Test	you@you.com	me@me.com	9/25/2020 09:29	9/25/2020 09:29	987ZYX	123ABC	2
Sent Items	Test	me@me.com	you@you.com	9/25/2020 08:45	9/25/2020 08:45	123ABC	null	1

So my question is what is my path forward to calculate the time between when the initial email was sent and when the first response was received? Of course, this is a miniscule example, the actual data is tens of thousands of rows long. Also, we're only interested in initial responses. Once someone responds to us they are more likely to maintain the conversation at a normal pace, which would dramatically skew the calculation. Is there a way to create a new column that is a count of the unique string generated for the InternetMessageID from the initial sent email? So the column I added at the end in red? I'm pretty new to these tools, so any suggestions are kindly welcome.

Thanks

Daniel

[v-xicai · Accepted solution]

Hi @danielgajohnson ,

You may create measure like DAX below.

Count of InternetMessageID =
CALCULATE (
    COUNT ( Table1[InternetMessageID] ),
    FILTER (
        Table1,
        Table1[Subject] = MAX ( Table1[ Subject] )
            && Table1[DateTimeSent] <= MAX ( Table1[DateTimeSent] )
    )
)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.