Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |