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

[NikkiSavage]

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

[Jihwan_Kim · Accepted solution]

Hi, @NikkiSavage 

Please check the below picture and the sample pbix file's link down below.

Hospital Within 2 days CC =
VAR currentcustomer = 'Table 1'[Customer Code]
RETURN
IF (
'Table 1'[Incident] = "Fall"
&& COUNTROWS (
FILTER (
'Table 2',
'Table 2'[Customer Code] = currentcustomer
&& 'Table 2'[Movement] = "Hospital"
&& 'Table 2'[Date] >= 'Table 1'[Date]
&& 'Table 2'[Date] <= 'Table 1'[Date] + 2
)
) > 0,
"Yes",
"No"
)

https://www.dropbox.com/s/leic0stepdpk5lj/nikki.pbix?dl=0 

Hi, My name is Jihwan Kim.

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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[parry2k · Accepted solution]

@NikkiSavage add new column using following expression:

Moved to Hospital in 2 days = 
VAR __days = 2
VAR __incidentDate = Incident[Date]
RETURN
IF (
    Incident[Incident] = "Fall" &&
    CALCULATE (
        COUNTROWS ( Movement ),
        TREATAS ( VALUES ( Incident[Customer Code] ), Movement[Customer Code] ),
        Movement[Date] >= __incidentDate,
        Movement[Date] <= ( __incidentDate + __days )
    ),
    "Yes",
    "No"
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Jihwan_Kim · Accepted solution]

Hi, @NikkiSavage 

Please check the below picture and the sample pbix file's link down below.

Hospital Within 2 days CC =
VAR currentcustomer = 'Table 1'[Customer Code]
RETURN
IF (
'Table 1'[Incident] = "Fall"
&& COUNTROWS (
FILTER (
'Table 2',
'Table 2'[Customer Code] = currentcustomer
&& 'Table 2'[Movement] = "Hospital"
&& 'Table 2'[Date] >= 'Table 1'[Date]
&& 'Table 2'[Date] <= 'Table 1'[Date] + 2
)
) > 0,
"Yes",
"No"
)

https://www.dropbox.com/s/leic0stepdpk5lj/nikki.pbix?dl=0 

Hi, My name is Jihwan Kim.

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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM