Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a table containing patients admission and discharge details. The objective is to find out patients re-admitted after discharge. If re-admitted in less than 5 days it should be alerted. I have calculated in excel manually, how to write DAX to achieve this result ? Just a comparison with previous row will not help, It should check the same paitent re-admission. In below example there is 1 patient re-admitted after 1 day.
Please help to find out a solution.
The sample data prepared in excel is given in the attached image.
Solved! Go to Solution.
Re Admission = VAR PreviousDischargeDate = CALCULATE ( MAX ( TableName[DISCHARGEDATE] ), FILTER ( ALLEXCEPT ( TableName, TableName[PatientID] ), TableName[Index] = EARLIER ( TableName[Index] ) - 1 ) ) RETURN IF ( NOT ( ISBLANK ( PreviousDischargeDate ) ), IF ( PreviousDischargeDate < TableName[AdmitDate], DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY ) ) )
Try this revised formula
Hi,
Super solution, thanks a lot, it works perfect.
Warmest regards Jay, Once again Thanks.
Hi @Jayadev
Try this solution
First add an Index Column for each PATIENTID. This will help in further calculations
Index = RANKX ( FILTER ( TableName, [PatientID] = EARLIER ( [PatientID] ) ), [AdmitDate], , ASC, DENSE ) Code beutified with Dax Formatter by SQLBI
Now you can get the Re-admission calculated column using this formula
Re Admission = VAR PreviousDischargeDate = CALCULATE ( MAX ( TableName[DISCHARGEDATE] ), FILTER ( ALLEXCEPT ( TableName, TableName[PatientID] ), TableName[Index] = EARLIER ( TableName[Index] ) - 1 ) ) RETURN IF ( NOT ( ISBLANK ( PreviousDischargeDate ) ), DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY ) )
Thanks a lot for your valuable inputs. I am getting the following error. Please help.
In DATEDIFF function, the start date cannot be greater than the end date
Regards,
Jay
Re Admission = VAR PreviousDischargeDate = CALCULATE ( MAX ( TableName[DISCHARGEDATE] ), FILTER ( ALLEXCEPT ( TableName, TableName[PatientID] ), TableName[Index] = EARLIER ( TableName[Index] ) - 1 ) ) RETURN IF ( NOT ( ISBLANK ( PreviousDischargeDate ) ), IF ( PreviousDischargeDate < TableName[AdmitDate], DATEDIFF ( PreviousDischargeDate, TableName[AdmitDate], DAY ) ) )
Try this revised formula
Hi,
Super solution, thanks a lot, it works perfect.
Warmest regards Jay, Once again Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
105 | |
86 | |
72 |