cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jonnyA
Post Patron
Post Patron

Create a Readmission Formula that only brings back accts where there was a readmission w/ in 90 days

Create a Readmission Formula that only brings back accts where there was a readmission w/ in 90 days.  Exclude accounts where admission and discharge 

 

For example, in the screen shot, all accounts, other than the yellow highlighted rows (Because the admittance and remit codes are greater than 90 days), should be in the matrix.

 

I need a formula, that will bring backl those results.  Thank you for your help in advance!

 

Admission Procedure Codes = 99221, 99222, 99223

Discharge Procedure Codes = 99238, 99239

 

Fields ...

  • ABC_Discharge Dataset
  • Patient Name
  • DOS
  • Procedure Code

jonnyA_0-1627413230027.png

 

 

 

2 ACCEPTED SOLUTIONS

Hi @jonnyA ,

 

I have broken the issue into two steps:

1. Create a calc column that gets the correlated admission and discharge dates for each row

2. Create a calc column that gets the absolute value of the date diff

3. filter the table by the calculated date difference

 

calc below:

admission_discharge_date = 
var _proc_code = 'ABC_Discharge Dataset'[Proc_Code]
var _dos = 'ABC_Discharge Dataset'[DOS]
var _patient_f_name = 'ABC_Discharge Dataset'[Patient_First_name]
var _patient_l_name = 'ABC_Discharge Dataset'[Patient_Last_Name]
return 
SWITCH(true(),
    _proc_code in {99238, 99239}, 
    CALCULATE(MAX('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99221, 99222, 99223})),
    _proc_code in {99221, 99222, 99223},
     CALCULATE(min('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]> _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99238, 99239})),
blank())
// CALCULATE(MAX('ABC_Discharge Dataset'[DOS]), FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos && _proc_code in {99221, 99222, 99223})))

admit_discharge_delta = ABS(DATEDIFF('ABC_Discharge Dataset'[DOS], 'ABC_Discharge Dataset'[admission_discharge_date],DAY))

 

Results in the following:

Non FIltered:

richbenmintz_0-1627498318146.png

Filtered:

richbenmintz_1-1627498387193.png

 

Attched please find a sample pbix.

 

hope this helps!

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi, @jonnyA ;

You could try to create a flag measure.

flag = 
VAR _next=
IF(
    MAX([Proc_Code]) in {99238, 99239}, 
    MAXX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]< MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99221, 99222, 99223}),[DOS]),
    MINX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]>MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99238, 99239}),[DOS]))
VAR _DIFF=IF(MAX([Proc_Code]) IN {99238,99239},DATEDIFF(_next,MAX([DOS]),DAY),DATEDIFF(MAX([DOS]),_next,DAY))
RETURN IF(_DIFF<90,1,0)

Then apply it into filter:

vyalanwumsft_0-1627612081001.png

The final output is shown below:

vyalanwumsft_1-1627612156080.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
jonnyA
Post Patron
Post Patron

Does anyone know how I can fix this measure??

jonnyA_0-1631813972834.png

 

jonnyA
Post Patron
Post Patron

@v-yalanwu-msft ,

Hello, and thank you for your help with the file you sent me, very helpful!

 

I cannot get the "Flag" measure to work on my end.  The formula has no errors from what I can tell, but when I add the formula to my matrix, the data goes blank.

 

Any suggestions on how to fix?

v-yalanwu-msft
Community Support
Community Support

Hi, @jonnyA ;

You could try to create a flag measure.

flag = 
VAR _next=
IF(
    MAX([Proc_Code]) in {99238, 99239}, 
    MAXX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]< MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99221, 99222, 99223}),[DOS]),
    MINX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]>MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99238, 99239}),[DOS]))
VAR _DIFF=IF(MAX([Proc_Code]) IN {99238,99239},DATEDIFF(_next,MAX([DOS]),DAY),DATEDIFF(MAX([DOS]),_next,DAY))
RETURN IF(_DIFF<90,1,0)

Then apply it into filter:

vyalanwumsft_0-1627612081001.png

The final output is shown below:

vyalanwumsft_1-1627612156080.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Fowmy
Super User
Super User

@jonnyA 

As per your data, there are three rows that exceed 90 days. Do you want to create a new table that shows only those three rows that I highlighted in yellow or you need a measure?

Fowmy_0-1627414882059.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@fowny,

I dont know why you are seeing 3 and i see 2 tho?

 

Keep in mind Admission codes vs Discharge.

 

So, if Admission Proc Code Date of Sevice is over 90 days from Discharge Procedure Code then those patients should be left off. 

@Fowmy 

I need a measure.  Thank you!

Hi @jonnyA ,

 

I have broken the issue into two steps:

1. Create a calc column that gets the correlated admission and discharge dates for each row

2. Create a calc column that gets the absolute value of the date diff

3. filter the table by the calculated date difference

 

calc below:

admission_discharge_date = 
var _proc_code = 'ABC_Discharge Dataset'[Proc_Code]
var _dos = 'ABC_Discharge Dataset'[DOS]
var _patient_f_name = 'ABC_Discharge Dataset'[Patient_First_name]
var _patient_l_name = 'ABC_Discharge Dataset'[Patient_Last_Name]
return 
SWITCH(true(),
    _proc_code in {99238, 99239}, 
    CALCULATE(MAX('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99221, 99222, 99223})),
    _proc_code in {99221, 99222, 99223},
     CALCULATE(min('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]> _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99238, 99239})),
blank())
// CALCULATE(MAX('ABC_Discharge Dataset'[DOS]), FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos && _proc_code in {99221, 99222, 99223})))

admit_discharge_delta = ABS(DATEDIFF('ABC_Discharge Dataset'[DOS], 'ABC_Discharge Dataset'[admission_discharge_date],DAY))

 

Results in the following:

Non FIltered:

richbenmintz_0-1627498318146.png

Filtered:

richbenmintz_1-1627498387193.png

 

Attched please find a sample pbix.

 

hope this helps!

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.