cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NikkiSavage
Helper I
Helper I

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

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 CodeIncidentDate Customer CodeMovementDate
XYZFall21/05/2021 XYZHospital23/05/2021
XYZBurn9/04/2021 XYZHospital11/11/2020
XYZFall11/01/2021 XYZOther9/09/2020
ABCFall20/04/2021 ABCOther1/01/2021
ABCLaceration29/12/2020 FGHHospital3/03/2021
       
Expected Data      
Table 1   
Customer CodeIncidentDateHospital within 2 days  
XYZFall21/05/2021Yes   
XYZBurn9/04/2021No   
XYZFall11/01/2021No   
ABCFall20/04/2021No   
ABCLaceration29/12/2020No   
2 ACCEPTED SOLUTIONS
Jihwan_Kim
Community Champion
Community Champion

Hi, @NikkiSavage 

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

 

Picture1.png

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

2 REPLIES 2
parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi, @NikkiSavage 

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

 

Picture1.png

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors