Filter through two many-to-many relationsip

[wicker]

Hi, everyone. I have a trouble with calculation through 2 many-to-many relationsip.

I have 3 tables like this:

leads				contacts			emails
id	contact_id	timestamp		id	email		email	timestamp
1	123	06.02.2020		123	123@site.com		123@site.com	01.02.2020
2	123	04.02.2020		123	123@site2.com		123@site.com	07.02.2020
							123@site2.com	05.02.2020
							123@site2.com	03.02.2020

Relationsips like this:

leads[contact_id] <-> contacts[id]

contacts[email] <-> emails[email]

I need to get for every row in table Leads last timestamp from Emails table, which earlier that timestamp in Leads table and email address related to the same contacts_id as lead.

For example, in row 1 I wanna get 05.02.2020, in row 2 03.02.2020

Pseudo code like this:

time = CALCULATE(MAX(emails[timestamp]);FILTER('contacts';'contacts'[id]='leads'[contact_id]) && FILTER(emails;'emails'[email]='contacts'[email]) && FILTER('emails';'emails'[timestamp] < 'leads'[timestamp]))

but I have DAX error on second filter condition.

[v-yuta-msft]

@wicker ,

---

I need to get for every row in table Leads last timestamp from Emails table, which earlier that timestamp in Leads table and email address related to the same contacts_id as lead.

For example, in row 1 I wanna get 05.02.2020, in row 2 03.02.2020

---

I'm not sure what you mean, could you please share the result table?

Regards,

Jimmy Tao

[wicker]

Hi, sure.

leads
id	contact_id	timestamp	max_date_before_lead_timestamp
1	123	06.02.2020	05.02.2020
2	123	04.02.2020	03.02.2020

I think some explanation for all 3 tables in start post are needed.

So. I have client, with 'contact'[id] 123

I have two leads with this contact - 'leads'[id] - 1 and 2

This client has 2 emails addresses - it's in table 'contacts'

This client sent me 4 emails from different email addresses - it's in table 'emails'

I want to find out, which email initiates the lead.

Lead 1 has date of creation 06.02.2020, so, I try to find last date before 06.02.2020 in all emails from all email_addresses related to this client.

[v-yuta-msft · Accepted solution]

@wicker ,

Create a calculate column in table leads using dax below:

Max_Date = 
VAR Current_id = leads[id]
VAR Current_contact_id = CALCULATE(MAX(leads[contact_id]), FILTER(leads, leads[id] = Current_id))
VAR Current_timestamp = CALCULATE(MAX(leads[timestamp]), FILTER(leads, leads[id] = Current_id))
VAR Mail_list = CALCULATETABLE(VALUES(contacts[email]), FILTER(contacts, contacts[id] = Current_contact_id))
RETURN
CALCULATE(MAX(emails[timestamp]), FILTER(emails, emails[timestamp] < Current_timestamp && emails[email] in Mail_list))

Community Support Team _ Jimmy Tao

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

[wicker]

Great! Thanks a lot!