rodwhiteley
New Member
- Joined
- Jan 15, 2012
- Messages
- 37
The reporting in our hospital's electronic medical record system is horrible, so I've exported some of the data, and I'm using Power BI to create reports and dashboards for the Physio department.
There are 2 main fact tables - one with all the encounters that patients have with doctors (fDr Appt below), and the same for encounters with physios (fPhysio Appt).
All patients have a GUID (their medical record number - MRN in the drawing of the simplified data model below) so both the Calendar and DISTINCT(fAppointments[MRN]) tables link the doctor appointments and physio appointment tables, similarly there's a table of distinct values for the physios and the doctors.
My aim for this task is to be able to display a table of all the doctors a selected patient has seen when a single value in a Patient list matrix (or other visualisation) is selected, otherwise return a blank table.
The selected MRN for the patient returns correctly with:
Selected MRN = SELECTEDVALUE(fPhysio Appt[MRN],BLANK())
But every attempt I've made to pass this Selected MRN back to the fDoctor Appointments table and then filter it to return only the doctors who have rows with a matching MRN is failing.
Clearly I'm misunderstanding something fundamental here regarding returning and visualising tables compared to scalars.
The idea would be, when the MRN is selected (in say the matrix below - MRN '01234') the adjacent table displays the filtered doctor appointment table with all the doctor names where MRN=Selected MRN (Dr Smith & Dr Death here)
Much kudos for any help,
Rod
There are 2 main fact tables - one with all the encounters that patients have with doctors (fDr Appt below), and the same for encounters with physios (fPhysio Appt).
All patients have a GUID (their medical record number - MRN in the drawing of the simplified data model below) so both the Calendar and DISTINCT(fAppointments[MRN]) tables link the doctor appointments and physio appointment tables, similarly there's a table of distinct values for the physios and the doctors.
My aim for this task is to be able to display a table of all the doctors a selected patient has seen when a single value in a Patient list matrix (or other visualisation) is selected, otherwise return a blank table.
The selected MRN for the patient returns correctly with:
Selected MRN = SELECTEDVALUE(fPhysio Appt[MRN],BLANK())
But every attempt I've made to pass this Selected MRN back to the fDoctor Appointments table and then filter it to return only the doctors who have rows with a matching MRN is failing.
Clearly I'm misunderstanding something fundamental here regarding returning and visualising tables compared to scalars.
The idea would be, when the MRN is selected (in say the matrix below - MRN '01234') the adjacent table displays the filtered doctor appointment table with all the doctor names where MRN=Selected MRN (Dr Smith & Dr Death here)
Much kudos for any help,
Rod