Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wicker
Frequent Visitor

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 
idcontact_idtimestamp idemail emailtimestamp
112306.02.2020 123123@site.com 123@site.com01.02.2020
212304.02.2020 123123@site2.com 123@site.com07.02.2020
       123@site2.com05.02.2020
       123@site2.com03.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.

1 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))

Capture.PNG

 

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.

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

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

@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))

Capture.PNG

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.