Filter through two many-to-many relationsip

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.