The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
77 | |
76 | |
36 | |
31 | |
29 |
User | Count |
---|---|
93 | |
79 | |
57 | |
48 | |
48 |