topic Re: Urgent Help with Creating a Flag column in DAX Commands and Tips

Urgent Help with Creating a Flag column

yve214 — Mon, 15 Aug 2022 03:57:41 GMT

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.

Re: Urgent Help with Creating a Flag column

tamerj1 — Mon, 15 Aug 2022 04:18:31 GMT

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 )

Re: Urgent Help with Creating a Flag column

yve214 — Mon, 15 Aug 2022 12:29:01 GMT

tamerj1 ,

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