Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
47 | |
16 | |
13 |