Forum Discussion

wicker's avatar
wicker
Frequent Visitor
6 years ago
Solved

Filter through two many-to-many relationsip

  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...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    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's avatar
wicker
Frequent Visitor
6 years ago

Hi, sure.

 

leads   
idcontact_idtimestampmax_date_before_lead_timestamp
112306.02.202005.02.2020
212304.02.202003.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's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    wicker
    Frequent Visitor
    6 years ago

    Great! Thanks a lot!