Forum Discussion
Iterate with LOOKUPVALUE
Hello all, I have been stuck on this for the whole afternoon, I feel like I'm just missing something...
I manage an email app data and have 3 tables:
Main Table
listing all events occurences
Send Tab
Detailing the "Send" event
Receive Tab
Detailing the "Receive" event
I'm trying to calculate the response time to any given email
=> Email Send date - Email Receive date
Email send date is: 'Send Tab'[Date]. Email receive date is: 'Receive Tab'[Date].
The relations between the tabs are through the main table with Event ID.
Here are the tables:
Main:
| Event ID | Date | Event type | |
| 1 | alexis | 27/3/18 15:45 | send |
| 2 | thibaud | 27/3/18 15:45 | receive |
| 3 | leonard | 27/3/18 15:45 | receive |
| 4 | Leonard | 27/3/18 16:06 | send |
| 5 | fabrice | 28/3/18 16:05 | receive |
| 6 | fabrice | 28/3/18 16:10 | receive |
| 7 | Alexis | 28/3/18 16:06 | receive |
| 8 | fabrice | 28/3/18 16:10 | send |
| 9 | fabrice | 28/3/18 16:15 | send |
Send Tab
| Event ID | Date | Message ID | In Reply To | |
| 1 | alexis | 27/3/18 15:45 | 123 | |
| 4 | leonard | 27/3/18 16:06 | 345 | 123 |
| 8 | fabrice | 28/3/18 16:10 | 789 | 456 |
| 9 | fabrice | 28/3/18 16:15 | 462 | 567 |
Receive Tab
| Event ID | Date | Message ID | |
| 2 | Thibaud | 27/3/18 15:45 | 123 |
| 3 | leonard | 27/3/18 15:45 | 123 |
| 5 | Alexis | 28/3/18 16:06 | 753 |
| 6 | fabrice | 28/3/18 16:05 | 456 |
| 7 | fabrice | 28/3/18 16:10 | 567 |
(Twice the same Msg ID cause the original msg had 2 recipients)
From what I understand I have to do
Response Time table =
LOOKUPVALUE(
'receive tab'[Date];
'receive tab'[Message ID];
'send tab'[In Reply To]
)
I get error "A TABLE OF MULTIPLE VALUES WAS SUPPLIED WHERE A SINGLE VALUE WAS EXPECTED"
I've tried to summarize by msg ID or iterate with filter through each cell but nothing is working...
The result I'm looking for is either a measure or a calculated column if we cannot directly do a measure.
EG: A column in "Send Tab" with response time if email is reply...
There will be a bit of data (500k rows+) so I need to try to find something optimized.
Hey ChrisMendoza,
Thanks for your answer, yes it is pretty much what I was looking for
I understood where I went wrong: I had duplicate values in the Search column.... So obvious...
The result I was looking for:
Original Email Received Date =
LOOKUPVALUE(
'receive tab'[Date];
'receive tab'[Message ID];
'send tab'[In Reply To];
'receive tab'[Email];
'send tab'[Email])
Thanks again Chris, idk how I struggled for 3 hours...
Best,
L
3 Replies
Anonymous,
Is your expected outcome for your example tables below?:
Hey ChrisMendoza,
Thanks for your answer, yes it is pretty much what I was looking for
I understood where I went wrong: I had duplicate values in the Search column.... So obvious...
The result I was looking for:
Original Email Received Date =
LOOKUPVALUE(
'receive tab'[Date];
'receive tab'[Message ID];
'send tab'[In Reply To];
'receive tab'[Email];
'send tab'[Email])
Thanks again Chris, idk how I struggled for 3 hours...
Best,
L
Hi Anonymous,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
