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
danielgajohnson
Helper II
Helper II

Initial Email Response from Exchange Connection

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 PathSubjectSenderRecipientDateTimeSentDateTimeReceivedInternetMessageIDInReplyToCount of InternetMessageID
InboxTestyou@you.comme@me.com9/25/2020 09:299/25/2020 09:29987ZYX123ABC2
Sent ItemsTestme@me.comyou@you.com9/25/2020 08:459/25/2020 08:45123ABCnull1

 

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

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

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.

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.