cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

1 ACCEPTED SOLUTION
Super User

Hi @yve214

``````Flag =
VAR CurrentIDTable =
CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[patient_Id] ) )
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 2
Super User

Hi @yve214

``````Flag =
VAR CurrentIDTable =
CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[patient_Id] ) )
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 )``````
Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors