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.
Relatively new to DAX and I am struggling to get my head around the solution to this problem. I have searched for a solution but nothing seems to fit,
I have 3 tables:
Patient provide this has the unique client ID in it
Hospital discharge table has the unique client ID, discharge date. Has many values for the same client ID - I have no control over this table. Comes from the hospital.
Schedule data also has the client ID, appointment dates, appointment types. This also has many fields.
There is no direct link other than client ID between these 2 tables. i.e there is no 'event ID' that would connect it to the associated follow up appointment.
What I need to get is the number of days from the patient's discharge date to the first outpatient appointment date (can't be the same day)
The results would look something like this:
Client ID | Discharge Date | Follow Up Date | Days |
112345 | 1/1/2022 | 1/5/2022 | 4 |
43235 | 4/1/2022 | 4/7/2022 | 6 |
532623 | 3/12/2022 | 3/14/2022 | 2 |
64452 | 2/14/2022 | 2/18/2022 | 4 |
426778 | 3/11/2022 | 3/20/2022 | 9 |
Median Days | 4 |
What I need for the report is the row total days and the aggregate median days per period FY, FYQ,...
Any insight would be very appreciated
Sorry about that
here is a link to the files:
https://1drv.ms/u/s!AptRNMy-TRKq4wvd6mrjQTUyIacP?e=y2mWAY
Here is what the output should look like:
PatientID | Event | Discharge Date | Follow Up Date | Days |
54918 | Emergency Discharge | 4/17/2021 | 4/28/2021 | 11 |
55069 | Emergency Discharge | 4/17/2021 | 4/26/2021 | 9 |
55079 | Emergency Discharge | 4/17/2021 | 4/22/2021 | 5 |
40509 | Emergency Discharge | 4/19/2021 | 4/29/2021 | 10 |
42195 | Emergency Discharge | 4/19/2021 | 4/28/2021 | 9 |
15283 | Inpatient Discharge | 01/31/2022 |
it is also possible that the person did not have a follow up appointment like patient 15283. I should be able to get the median days to follow up by both patient and as an aggregate by time period.
Hello @jpvernon67 ,
Request you to please provide sample data of all the tables and expected result so that we can help you.
Thanks,
Shishir
here is a link to the files:
https://1drv.ms/u/s!AptRNMy-TRKq4wvd6mrjQTUyIacP?e=y2mWAY
Here is what the output should look like:
PatientID | Event | Discharge Date | Follow Up Date | Days |
54918 | Emergency Discharge | 4/17/2021 | 4/28/2021 | 11 |
55069 | Emergency Discharge | 4/17/2021 | 4/26/2021 | 9 |
55079 | Emergency Discharge | 4/17/2021 | 4/22/2021 | 5 |
40509 | Emergency Discharge | 4/19/2021 | 4/29/2021 | 10 |
42195 | Emergency Discharge | 4/19/2021 | 4/28/2021 | 9 |
15283 | Inpatient Discharge | 01/31/2022 |
it is also possible that the person did not have a follow up appointment like patient 15283. I should be able to get the median days to follow up by both patient and as an aggregate by time period.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |