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

[DavidArthurBaum]

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:  

Here are the tables:

Customer

Customer ID	Name
US100100	James
US100200	Rick

Activity Table

Customer ID	Activity Date	Activity Type	Activity Detail
US100100	Tuesday, June 2, 2020	Email	Follow-Up
US100200	Thursday, February 20, 2020	Call	Follow-Up
US100200	Tuesday, June 2, 2020	Call	Follow-Up
US100100	Saturday, May 2, 2020	Email	Follow-Up
US100100	Sunday, February 2, 2020	Call	Other
US100100	Monday, February 10, 2020	Call	Follow-Up
US100100	Saturday, February 15, 2020	Call	Follow-Up

Engagement Table

Customer ID	Engagement Date	Engagement Code	Interest Level
US100100	Saturday, February 1, 2020	Event	High
US100100	Friday, May 29, 2020	Demo	Other
US100200	Saturday, February 1, 2020	Event	Med
US100200	Friday, May 29, 2020	Demo	High
US100100	Friday, May 1, 2020	Web Session	High
US100200	Friday, May 1, 2020	Web Session	Med

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

Result

Name	Activity Date
James	2/10/2020
Rick	6/2/2020

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

[amitchandak · Accepted solution]

@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

[DavidArthurBaum]

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

[AllisonKennedy]

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