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
Anonymous
Not applicable

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
Super User

@Anonymous , 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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

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

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.