Forum Discussion
Helper IHelp on how to Generate These Measures
Alright, here's your DAX for NewAdmission, ReAdmission and Total_Admissions
I was a little unsure how you wanted to handle the situation where the same patient comes in more than once on the same day, especially if that's the first time the patient has been seen.
So the way I did it, if a patient is seen for the first time on a certain day and they go in more than once on that day, ONE will count as an admission, and the rest will count as a re-admission.
Also, if the same patient is seen twice in one month, they both will count. (Potentially one admission and one readmission, or both readmission, or whatever the case may be).
First, you need to add an index column in Power Query (It's easy just select New column and at the top click Add Index Column).
NewAdmission =VAR ranktable =ADDCOLUMNS ('sample',"first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),"DayRank", RANKX (FILTER ('sample','sample'[PatientID] = EARLIER ( 'sample'[PatientID] )&& 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )),'sample'[Index],,ASC,Dense))VAR result =SUMX (FILTER (ranktable,'sample'[FullDate] = [first_Date]&& [DayRank] = 1),[AdmitCount])RETURNCOALESCE(result, 0)ReAdmissions =
VAR ranktable =ADDCOLUMNS ('sample',"first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),"DayRank", RANKX (FILTER ('sample','sample'[PatientID] = EARLIER ( 'sample'[PatientID] )&& 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )),'sample'[Index],,ASC,Dense))VAR result =SUMX (FILTER (ranktable,'sample'[FullDate] > [first_Date]|| ( 'sample'[FullDate] = [first_Date] && [DayRank] > 1 )),[AdmitCount])RETURNCOALESCE(result, 0)
total_admissions = [NewAdmission] + [ReAdmissions]
Then, to filter the dates in the table, you need to create this measure:Table_Filter =VAR year = SELECTEDVALUE(DateDim[YEAR])VAR MONTH = SELECTEDVALUE(DateDim[Month_Num])VAR START_OF_MONTH = DATE(year, MONTH, 1)VAR END_OF_MONTH = EOMONTH(START_OF_MONTH, 0)var selected_date = SELECTEDVALUE('sample'[FullDate])RETURNIF ( selected_Date >= START_OF_MONTH && selected_date <= end_of_month, 1, 0)And you need to add this as a visual-level filter, only include rows where it equals 1.
Attached is the .pbix file.
///MEDIOCRE POWER BI ADVICE, BUT IT'S FREE///
Helper IThank you. Here's additional info:
Using the initial dataset, with slicer set for APR 2025, my (1) [TotalAdmissions] is just the sum of the DS.[AdmitCount] per day.
The [NewAdmission] and [ReAdmission] just evaluates if the admitted patient for the MONTH+YEAR is a new patient or an old patient.
[NewAdmission] + [ReAdmission] = [TotalAdmissions]
So for [ReAdmission], lets take the APRIL 4 as an example.
BANANA was admitted on 4/4/25. He is considered a ReAdmission because he had a prior admission record on 7/8/2024 (total admission record >1).
So for [NewAdmission], lets take the APRIL 4/8 as an example.
MELON was admitted on 4/8/25. He is considered a NewAdmission because he had no prior admission record (total admission record =1).
Solution SageAlright, here's your DAX for NewAdmission, ReAdmission and Total_Admissions
I was a little unsure how you wanted to handle the situation where the same patient comes in more than once on the same day, especially if that's the first time the patient has been seen.
So the way I did it, if a patient is seen for the first time on a certain day and they go in more than once on that day, ONE will count as an admission, and the rest will count as a re-admission.
Also, if the same patient is seen twice in one month, they both will count. (Potentially one admission and one readmission, or both readmission, or whatever the case may be).
First, you need to add an index column in Power Query (It's easy just select New column and at the top click Add Index Column).
NewAdmission =
ReAdmissions =
total_admissions = [NewAdmission] + [ReAdmissions]
Then, to filter the dates in the table, you need to create this measure:
And you need to add this as a visual-level filter, only include rows where it equals 1.
Attached is the .pbix file.
///MEDIOCRE POWER BI ADVICE, BUT IT'S FREE///