Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |