cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidArthurBaum
Frequent Visitor

compare date fields in 2 tables, each related to a 3rd table and keep visual level filters

Hi everyone!  I'm struggling with a comparison between date fields in 2 separate tables.

 

I have 3 tables in total: Customer, Activity, and Engagement.  The Activity and Engagement tables are related to Customer via Customer ID, but have no relationship to each other. 

 

What I am trying to do:

  1. Use visual level filters to keep only Interest Level = High or Med, Activity Type = Call, and Activity Detail = Follow-Up
  2. Compare the Activity Date and Engagement Date
  3. Return Activity Date if the Activity Date is within 10 days of the Engagement Date.   For example, if the Engagement Date was Jan 1 2020, the Activity Date needs to be Jan 1 2020 - Jan 11 2020 as well as satisfy criteria from #1 above.

 

Here is how the relationships look in the model view:  

Capture2.PNG

 

Here are the tables:

Customer

Customer IDName
US100100James
US100200Rick

 

Activity Table

Customer IDActivity DateActivity TypeActivity Detail
US100100Tuesday, June 2, 2020EmailFollow-Up
US100200Thursday, February 20, 2020CallFollow-Up
US100200Tuesday, June 2, 2020CallFollow-Up
US100100Saturday, May 2, 2020EmailFollow-Up
US100100Sunday, February 2, 2020CallOther
US100100Monday, February 10, 2020CallFollow-Up
US100100Saturday, February 15, 2020CallFollow-Up

 

Engagement Table

Customer IDEngagement DateEngagement CodeInterest Level
US100100Saturday, February 1, 2020EventHigh
US100100Friday, May 29, 2020DemoOther
US100200Saturday, February 1, 2020EventMed
US100200Friday, May 29, 2020DemoHigh
US100100Friday, May 1, 2020Web SessionHigh
US100200Friday, May 1, 2020Web SessionMed

 

Based on my hand-drawn version on paper, the result should be as shown below.

Result

NameActivity Date
James2/10/2020
Rick6/2/2020

 

Thanks in advance.  Any help is greatly appreciated.  I'm new to Power BI - David

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@DavidArthurBaum , Create a new flag column in activity table and filter for 1

 

new column =
var _cnt = countx(filter(Engagement , Engagement[Engagement Date] <=Activity[Activity Date] && Engagement[Engagement Date] >=Activity[Activity Date] -10 &&
Engagement[Customer ID] = Activity[Customer ID]),Engagement[Customer ID])+0
return
if(_cnt >0, 1, 0)

 

Use customer and activity in visual with filter =1 for new column



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@DavidArthurBaum , Create a new flag column in activity table and filter for 1

 

new column =
var _cnt = countx(filter(Engagement , Engagement[Engagement Date] <=Activity[Activity Date] && Engagement[Engagement Date] >=Activity[Activity Date] -10 &&
Engagement[Customer ID] = Activity[Customer ID]),Engagement[Customer ID])+0
return
if(_cnt >0, 1, 0)

 

Use customer and activity in visual with filter =1 for new column



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak Beautiful!  This works.  Thank you so much for your help.  Kudos + Solution Accepted

AllisonKennedy
Super User III
Super User III

@DavidArthurBaum  You are right that these two tables don't filter each other. I suggest you might want to set the cross filter direction to single as well. You can add visual level filters to a table visualization that can filter from one fact to another by using COUNTROWS. See if this post and video help explain how: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ 



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.