Flagging Hospital Readmissions with PowerPivot

[dldenton]

I'm trying to develop a means of calculating hospital readmissions. I'd like to keep as much of the calculation as possible in PowerPivot. I have a functional solution at the moment, but it uses a couple array functions and helper columns that become quite slow when large amounts of data are involved.

Objective: list all patients who are re-admitted to the hospital within 30 days of discharge.
Criteria: initial admission must be for heart failure (HF), subsequent admission can be for any reason.

Data:
PatientData (attachment) lists a unique patient identifier (MRN), a unique visit identifier (FIN), dates, and diagnosis codes.
CodeData (attchment) list all the diagnosis codes that indicate heart failure.

I've been working on this one for a couple days now. My current solution involves adding two helper columns to the PatientData table. One that flags heart failure admissions, and another that flags possible readmissions by the same patient. The array functions involved are really slowing things down, however. Any help would be much appreciated.

Thanks.

[entonne]

Late reply to an old post, but in case anyone else stumbles across this...

Assumptions:

• Your data is in a table called Data in PowerPivot
• Your lookup table is called CodeData in PowerPivot
• A relationship has been created in the data model between your data table (Data) and your lookup table (CodeData) based on the Code field
• Readmission time frame is 30 days

1. Create a measure (calculated field in Excel 2013): M_DISCHARGE_COUNT

Please Login or Register  to view this content.

2. Create a calculated column (in the PowerPivot table): C_DISCH_DATE+30

Please Login or Register  to view this content.

This is a helper column used for the next calculation that adds 30 days to the discharge date. You could simply use this formula in the next calculation instead of creating a column, but I prefer to keep it separated to visualize what is going on.


3. Create a calculated column (in the PowerPivot table): C_NUMBER_OF_READMISSIONS
This calculates the number of readmissions within a 30 day period for each row of data. If you have 4 admissions within 30 days for the same patient, the output would be:
1/1/2015 3
1/3/2015 2
1/5/2015 1
1/7/2015 null

Please Login or Register  to view this content.

4. Create a calculated column (in the PowerPivot table): C_READMISSION_COUNTER
Since the previous calculation gives us total readmissions and duplicates values, we need a helper column to drag into the pivot table. We will also address the Heart Failure requirement.

Please Login or Register  to view this content.

5. Create a pivot table in Excel, drag in MRN and FIN as your row criteria and drag C_READMISSION_COUNTER as your values (or create an additional measure summing C_READMISSION_COUNTER to avoid implicit calculations). Any row in your pivot table that shows a 1 means there is a subsequent admission within 30 days for all heart failure admissions.


I've tried to do this with strictly measures (calculated fields in Excel 2013) and not columns, but can't find an efficient way to do it. The key is the EARLIER() function. If there are any questions feel free to respond (or if there is a better way). Hope it helps!

[njkamenos]

I'm working with Microsoft 2010...and I get the attached error. Any ideas?

The value for column 'M_DISCHARGE_COUNT' in table 'PatientData' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

[doughnuted]

Hi guys, I'm posting here as it's a similar problem.

I want to calculate the readmission rate for a particular problem. I have a table with 35k rows, 100 columns, where each row represents an admission. The three columns of interest are A- patient ID, B- admission date, C- discharge date. What I want to generate is one column that tells me whether a patient was admitted within 30 days after the current admission (by discharge date), and one column that tells me the number of days between discharge and the next admission.