Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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 )
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 )
@tamerj1 ,
you are a life safer. YES worked PERFECTLY. Immense thanks for your quick and effective response.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
18 | |
17 |
User | Count |
---|---|
33 | |
25 | |
18 | |
15 | |
13 |