Cross reference to another table with multiple lines for the same customer and multiple dates

Question

Hi.I have two tables - one has data in it relating to customers, incidents and dates. Some customers have multiple incidents so they can be in multiple rows of data. The other table shows movements of customers and the dates they moved. I want to add a new column to my first table which detects whether a customer has gone to hospital only (within movement column) for falls only (within incident column) within 2 days of the incident occurring. Example tables are below for current and expected data:Current DataTable 1&nbsp;Table 2Customer CodeIncidentDate&nbsp;Customer CodeMovementDateXYZFall21/05/2021&nbsp;XYZHospital23/05/2021XYZBurn9/04/2021&nbsp;XYZHospital11/11/2020XYZFall11/01/2021&nbsp;XYZOther9/09/2020ABCFall20/04/2021&nbsp;ABCOther1/01/2021ABCLaceration29/12/2020&nbsp;FGHHospital3/03/2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Expected Data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table 1&nbsp;&nbsp;&nbsp;Customer CodeIncidentDateHospital within 2 days&nbsp;&nbsp;XYZFall21/05/2021Yes&nbsp;&nbsp;&nbsp;XYZBurn9/04/2021No&nbsp;&nbsp;&nbsp;XYZFall11/01/2021No&nbsp;&nbsp;&nbsp;ABCFall20/04/2021No&nbsp;&nbsp;&nbsp;ABCLaceration29/12/2020No&nbsp;&nbsp;&nbsp;

jihwan_kim · Accepted Answer

Hi,&nbsp;NikkiSavage&nbsp;Please check the below picture and the sample pbix file's link down below.&nbsp;&nbsp;Hospital Within 2 days CC =VAR currentcustomer = 'Table 1'[Customer Code]RETURNIF ('Table 1'[Incident] = "Fall"&amp;&amp; COUNTROWS (FILTER ('Table 2','Table 2'[Customer Code] = currentcustomer&amp;&amp; 'Table 2'[Movement] = "Hospital"&amp;&amp; 'Table 2'[Date] &gt;= 'Table 1'[Date]&amp;&amp; 'Table 2'[Date] &lt;= 'Table 1'[Date] + 2)) &gt; 0,"Yes","No")&nbsp;&nbsp;https://www.dropbox.com/s/leic0stepdpk5lj/nikki.pbix?dl=0&nbsp;&nbsp;&nbsp;Hi, My name is Jihwan Kim. &nbsp;If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up. &nbsp;Linkedin: linkedin.com/in/jihwankim1975/Twitter: twitter.com/Jihwan_JHKIM

Forum Discussion

Cross reference to another table with multiple lines for the same customer and multiple dates

2 Replies

Related content

Power BI custom visual not sending Referer

Custom Reference Line Using DAX

multiple customer design in fabric

Configure RLS for multiple external customers?

dynamic reference line

Current Data
Table 1		Table 2
Customer Code	Incident	Date		Customer Code	Movement	Date
XYZ	Fall	21/05/2021		XYZ	Hospital	23/05/2021
XYZ	Burn	9/04/2021		XYZ	Hospital	11/11/2020
XYZ	Fall	11/01/2021		XYZ	Other	9/09/2020
ABC	Fall	20/04/2021		ABC	Other	1/01/2021
ABC	Laceration	29/12/2020		FGH	Hospital	3/03/2021

Expected Data
Table 1
Customer Code	Incident	Date	Hospital within 2 days
XYZ	Fall	21/05/2021	Yes
XYZ	Burn	9/04/2021	No
XYZ	Fall	11/01/2021	No
ABC	Fall	20/04/2021	No
ABC	Laceration	29/12/2020	No