Urgent Help with Creating a Flag column

[yve214]

Problem:

Please, I am trying to check if my recent admission location is the same as my recent discharge location and create a flag to help me with my other measures. How to approach this.

check...

1. The patient's most recent admission location

2. The patient's most recent discharge location

3. Create a flag to see if they have the same location then 1, else 0

Table:

patient_name	patient_Id	admission_date	discharge_date	record_Id	Location
Mark	1	1/1/2018	3/15/2019	1001	A
John	2	6/1/2019	6/3/2020	1002	B
Tom	3	1/1/2020	8/7/2020	1003	A
Tom	3	8/7/2020	9/7/2020	1004	C
Tom	3	9/7/2020	10/3/2020	1005	A
Sarah	4	7/2/2015	2/1/2018	1006	C
Kim	5	3/1/2016	7/6/2017	1007	E
Mark	1	3/16/2019	4/7/2019	1008	A
Tom	3	2/1/2021		1009	C
Steve	6	3/2/2019		1010
John	2	3/4/2022	5/10/2022	1011	A

Expected Results:

patient_name	patient_Id	location_flag	//Explanation
Mark	1	1	most recent discharge location and new admission location is the same so flag is 1
John	2	1	recent discharge location is same as recent admission
Tom	3	0	different locations for  their recent admission and discharge record
Sarah	4	0	has only one record
Kim	5	0	has only one record
Steve	6	0	has only one record, no discharge record

Thank you very much in advance.

[tamerj1 · Accepted solution]

Hi @yve214 

please try

Flag =
VAR CurrentIDTable =
    CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[patient_Id] ) )
VAR LastAdmissionLocation =
    MAXX ( TOPN ( 1, CurrentIDTable, Table1[admission_date] ), Table1[Location] )
VAR LastDischargeLocation =
    MAXX ( TOPN ( 1, CurrentIDTable, Table1[discharge_date] ), Table1[Location] )
RETURN
    IF ( LastAdmissionLocation = LastDischargeLocation, 1 )

[yve214]

@tamerj1 ,

you are a life safer. YES worked PERFECTLY. Immense thanks for your quick and effective response.