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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Taking the min with condition

table 1 have email sent date:

SentDate.JPG

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

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! 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

=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).

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank you for your help.

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.  

 

error_filter.JPGRelationship.JPG

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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

 

Thank you so much. 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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