Taking the min with condition

[Anonymous]

table 1 have email sent date:

table 2 has when they visited the website and they could have visited multiple times.

The two tables have ID relationship.

I would like to pull the first login date from table 2 that occurs after the sent date into table one.  Know that logins could happen before or after the email sent, but I would like to know who log in within 7 days after the email sent out so that we could measure campaign effectiveness.   

My approach is to take the min(date) from table 2 and call it into a column on table 1, but how do I add a condition to make sure the min(date) be greater than the send date.  

Thank you very much for any help! 

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in Table1.  You will need to change the Table and column names

=CALCULATE(MIN(Table2[Date]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Date]>EARLIER(Table1[Date])))

Hope this helps.

[Anonymous]

=CALCULATE(MIN(Table2[Date]),FILTER(Table2,Table2[ID]=EARLIER(Table1[ID])&&Table2[Date]>EARLIER(Table1[Date])))

When filtering table 2, including table 1 causes error.  Please advise (red text above).

[Ashish_Mathur]

Hi,

I cannot figure out the mistake there.  Share the link from where i can download the PBI file with my formula already written there.  I will try to correct for the error.

[Anonymous]

Thank you for your help.

Here is the link: http://35.188.212.139/test_measure.pbix

[Ashish_Mathur]

Hi,

Edit the "Cross filter direction" in both relationships to Single.  My calulcated column formula will give the correct result.

[Anonymous]

could you send me the working PBIX?  I still have an error stating "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".  I'm not sure that Filter() can call multiple tables.  

[Ashish_Mathur]

Hi,

You get that error because you are writing that as a measure.  As clearly stated in my previous message, please write your formula as a calculated column formula.  Change the Direction of both relationships from Both to Single.  Here is a screenshot of the result.

[Anonymous]

Wow... My fault... I didn't know that calculated measure couldn't do what column could. 

Thank you so much. 

[Ashish_Mathur]

You are welcome.