Forum Discussion
Solved
Urgent Help with Creating a Flag column
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.
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 )
2 Replies
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 )
