Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |