Get Number of patients in hospital who have stayed >21 days based on admit_date and discharge_date

[seanosullivan]

Hi, I have a hospital inpatients table with over a million rows. Each row holds details of the patients stay including their admit_date and discharge_date. The table has a column that holds the length of stay (LoS) as '+21' for patients whose stay is over 21 days.

I have a measure that calculates patient activity that counts the rows (Pat Activity)

I have a Calendar Table with all the dates in the Date field. Tb_Calendar[Date]

I want to get a new measure that calculates for any date in my calendar table how many patients have a LoS +21

In searches I have seen people creating embeddded tables but when we have so many rows I do not think this will be workable, so hope someone has another option.

Here is a very small extract of the Table. 

The output should show the following values for the tables, BUT I only want to see the relevant value for a given day in a measure so that I can put it in a graph visual to spot trends 

Eg With 10/11/21 I would like to see 3 returned for the LoS of '+21'

[daXtreme]

// Assuming you only want this to work for
// individual dates and not for any other
// chunks of time... and assuming that you
// have a disconnected Dates table...
[Num Of 21+] =
IF ( HASONEFILTER( Dates[Date] ), -- checks if only 1 date is active
    var CurrentDate = SELECTEDVALUE( Dates[Date] ) -- retrieves the date
    var PatientCount =
        CALCULATE(
            COUNTROWS( Patients ),
            KEEPFILTERS( Patients[Admit_Date] <= CurrentDate ),
            KEEPFILTERS( CurrentDate <= Patients[Discharge_Date] ),
            KEEPFILTERS( Patients[LoS] = "21+" )
        )
    return
        PatientCount
)

Patients is the fact table that you show above.

[tackytechtom]

Hi @seanosullivan ,

This is what I got:

As @m3tr01d was suggesting as well, I used the calendar / date dimension which shall not be connected to your table. It is quite many measures, but I believe some of them should give you the results you expected, At least, that's what I hope 😄

TomsCountPatientsMeasure = 
VAR _date = LASTDATE ( 'date'[Dates] )
RETURN
CALCULATE (
    COUNTROWS ( table16 ),
   _date >= Table16[Admit_Date] && _date <= Table16[Discharge_Date]
)

TomsCountPatients>21Measure = 
VAR _date = LASTDATE ( 'date'[Dates] )
RETURN
CALCULATE (
    COUNTROWS ( table16 ),
   _date - 21 >= Table16[Admit_Date] && _date <= Table16[Discharge_Date]
)

TomsCountPatients<21Measure = 
VAR _date = LASTDATE ( 'date'[Dates] )
RETURN
CALCULATE (
    COUNTROWS ( table16 ),
   _date >= Table16[Admit_Date] && _date <= Table16[Discharge_Date],
   NOT ( _date - 21 >= Table16[Admit_Date] )
)

TomsCountPatients>21Measure2 = 
VAR _date = LASTDATE ( 'date'[Dates] )
RETURN
CALCULATE (
    COUNTROWS ( table16 ),
   _date  >= Table16[Admit_Date] && _date <= Table16[Discharge_Date],
   Table16[LoS] = "21+"
)

TomsCountPatients<21Measure2 = 
VAR _date = LASTDATE ( 'date'[Dates] )
RETURN
CALCULATE (
    COUNTROWS ( table16 ),
   _date >= Table16[Admit_Date] && _date <= Table16[Discharge_Date],
   NOT (    Table16[LoS] = "21+" )
)

I believe you were looking for the measures that end with "2". But I found it more logical to go for the ones that do not end with "2" since they tell you how long your patients have been in the hospital (longer than 21 days / less than 21 days) on that exact day. 

Let me know if this helps! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[m3tr01d]

@seanosullivan 

Maybe, you could try this measure and see if it works :

Count_Measure = 
VAR _Date_Showing = MIN( 'Date'[Cal Date] )
RETURN
CALCULATE(
	COUNTROWS( 'Pat Activity' ),
	'Pat Activity'[Admit_Date] >= _Date_Showing,
	'Pat Activity'[Discharge_Date] <= _Date_Showing,
	ALL( 'Calendar'[Date] )
)

Depending on what kind of fields of the Calendar table is used on the page/visual, we might need to include more columns in the ALL( * ) clause.

[seanosullivan]

Hi @m3tr01d , 

This is close, I created the following measure based around your idea:

In Patients:=VAR _Date_Showing = FIRSTDATE('Tb_Calendar'[Date])

RETURN

CALCULATE(
COUNTROWS(Tb_IP),
'Tb_IP'[Admission_Date] >= _Date_Showing,
'Tb_IP'[Discharge_Date] < _Date_Showing,
ALL( 'Tb_Calendar'[Date])
)

I adjusted for Discharge Date has only to be less than as they are not in Hospital at end of that day. 

I then created Measures for the different LoS's:

LOS 14-20:=CALCULATE([In Patients],Tb_IP,'Tb_IP'[LoS Bands]="14-20")

It is working for some but not for others 

Will work on it more tomorrow

[seanosullivan]

@m3tr01d Just to clarify, is the Date table you are referencing in the variable another disconnected date table or the same date table I have altready?

[m3tr01d]

@seanosullivan  Oups, it would be the same calendar table, we don't need to create a new one.

[seanosullivan]

In my solution I need to emulate the Excel formula below

=COUNTIFS(Tb_IP[Admit_Date],"<="&[@Date],Tb_IP[Discharge_Date],">"&[@Date],Tb_IP[LoS],Output[[#Headers],[21+]])

In short, If LoS is '21+' and todays date is between the 'Admit_date' and the 'Discharge_Date' then I want to include the row (not just on their admit date)

Hope this helps.

@m3tr01d @Whitewater100 @tackytechtom 

[Whitewater100]

Hi:

Can you use

COUNTROWS(

                     FILTER( yourtable, 
                        Table[Column LOS] = "LOS +21")
)

You have to plug in your table name and column name for LOS +21. If it says 21 and it's a number than the last part of the measure is Table[Column LOS] = 21))

[tackytechtom]

Hi @seanosullivan ,

I would suggest to create a measure like this:

Measure =
CALCULATE ( COUNTROWS ( table ), 'table'[LoS] = '+21' )

And then you should be able to use the measure with your date / calendar table.

Does this help? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[seanosullivan]

Hi Sorry, that would have been easy, I have added the table and output to make it clearer

[m3tr01d]

hi @seanosullivan 

can you tell us what is not working with @tackytechtom 's solution?. I feel that it should give you what you want. Do you already have that column LoS built inside the model?

Thank you

[seanosullivan]

Sorry yes as you will see from the updated post the LoS column is already in my table from SQL. 

also tagging @tackytechtom 

[seanosullivan]

Hi @m3tr01d 

That is what I am currently graphing and it is wrong, hence why I am coming here...

The above solution only shows the LoS count on the day the patient is admitted (Which is my Date Key to the calendar table)

I need it to count that person as over 21 days for the entire duration of their stay in hospital. Since they are using up a bed for 21 days.  

For now I want to ignore the problem where a patient has been admitted for a long time and has no discharge date...I will worry about that issue later.

[m3tr01d]

@seanosullivan  Ok, I think I understand now, If a patient has been admited from March 3rd to March 6th, you'll want to see this patient counting for March 3rd, March 4th, March 5th, March 6th because he was occupying a bed in the hospital.

Hmm, it seems to me that you might need to have a disconected date table. I'll take a look and see if I can find something. Hopefully, someone else will give you a solution 🙂