Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jonnyA
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

Does anyone know how I can fix this measure??

jonnyA_0-1631813972834.png

 

jonnyA
Responsive Resident
Responsive Resident

@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.

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

jonnyA
Responsive Resident
Responsive Resident

@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. 

jonnyA
Responsive Resident
Responsive Resident

@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!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.