Iterate with LOOKUPVALUE

[Anonymous]

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	Email	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	Email	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	Email	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. 

[ChrisMendoza]

@Anonymous,

Is your expected outcome for your example tables below?:

[Anonymous · Accepted solution]

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

[v-piga-msft]

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