Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | 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.
Solved! Go to 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 ,
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
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.
@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.
Great! Thanks a lot!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |