Display (text) table filtered by 2 criteria, each from different tables


#1

I’m making a report for our rehabilitation department within a hospital, and need to display a filtered list, based on 2 separate criteria.

Some background might help: patients come to our hospital, see 1 or more doctors, and then get referred to rehabilitation to see therapists.

One report I’ve made filters all of the fTherapistVisits table to show the selected therapist (from the dimension table: dTHERAPIST[Therapist Name]) and their respective current list of patients (each of which have a unique Medical Record Number - MRN). What I’d like to be able to do, is when a Single therapist is selected (in the Therapist slicer, “Jane” in the drawing below) AND a single Patient is selected in the table (which is now filtered as Jane’s active patients, with “Pierre” selected here) then in the separate “Treating Doctor” table, show all the doctors who this patient has seen - in this case there are visits associated with Dr Smith, Dr Death, and Dr Who.

Currently, I can create the therapist slicer and show the current therapist list of patients, but can’t get the associated doctors to display. I’ve shown below a schematic of the tables - fact tables at the bottom, and dimension tables at the top (I can’t show the actual screen grab, or ‘real’ reports because of patient privacy laws)

All help gratefully recevied,

Rod



#2

Interesting one,

Might need to try a few things here.

I’m presuming we need to dynamically in the formula get rid of the therapist context because when you selected a patient you want to see all there doctors regardless of the therapist.

Let’s also for the time being start with a table, and use the dimension DoctorName in it first. Well place this measure next to it.

Let’s create a measure that counts the name of visits to a doctor and see if this atleast filter the table of doctors (as blank values won’t be returned)

CALCULATE( COUNTROWS( DoctorVisits ), ALL( DoctorVisits[TherapistName] )

Try this measure and place it next to the DoctorName dimension in a table. I want to see if this filter that table correctly.


#3

Thanks for getting back to me Sam,
I feel like I can see the finish line, but don’t know how to get there.
In the attached report screen grab, I have placed the measure:
Dr visits = CALCULATE(COUNTROWS(fDrAppts),ALL(fDrAppts[Therapist]))
against the Dr Name (from the Dr name dimension table) (“1 Dr Name” in red, top left)
Then when I choose a therapist name from the slicer (2) that therapist’s individual patient attendance history data filters correctly (in (3)), and the Dr Name table is filtering back to all Dr’s who have seen patients that have also seen this therapist. What I need to figure out is how to filter this list of Dr Names such that when I select an individual patient from “3 Patient Name and visit history”, the Dr names will then filter to meet the condition that they have a visit from this individual patient only. Currently, when selecting individual patient names, the Dr name/Dr visits table (1) is staying the same.
My guess is that I need to have some combination of “HASONEVALUE” and then make the Dr Visits Measure apply both these filters (SELECTEDVALUE for both 2 and 3)


#4

I’ve re-read your post about 8 times now, and to me the logic works out and you should be getting what you need.

So if not I need more clarification.

I’ve also mock it up myself and seem to get the right results.

Just to confirm.

Based on a selection in the ‘patient name & visit history’ we are trying to find a list of doctors who have seen this patient, regardless of the therapist selection.

(we are just using the number of visits as a placeholder calc at the moment)

Based on the logic I am imagining this is producing the correct results.

What am I now understand from this -

What I need to figure out is how to filter this list of Dr Names such that when I select an individual patient from “3 Patient Name and visit history”, the Dr names will then filter to meet the condition that they have a visit from this individual patient only

Here’s my mock up testing the ALL part of it.


#5

Thanks again for looking at this Sam, and apologies for wasting your time if I’ve simply done something stupid here (there’s every chance).
I’ve had to mask all personally identifiable information here (patient, doctor, and therapist names) obviously.
Hopefully you’ll see that the doctor name table is filtering for the first selection (when I choose a therapist) but not the second condition (when I choose an individual patient from the table).
I’ve made an animation up here showing the data model, thee relevant tables, and the current function of the report. It’s too big to upload here, so here’s a link:
https://1drv.ms/v/s!AgytcDfT4DRFhMcUF8A3YlcO6Jpyjw
Thanks again for any help,
Rod


#6

Got it! I think. Great video by the way, helps a lot. Really showcases how it can be very hard to know how to solve these without getting the full picture…the model especially

The reason it’s not working it the MRN column in table 3 is coming from the fAppointments table, and the formula is calculating over the fDtAppts. So when your placing the final filter on it’s doing nothing, because there is no relationship between these two tables.

You need to use the MRN from the lookup table, and it should work.

image


#7

Thanks Sam - perfect.
I’ve really got to better grok Data Modelling - examples like this help.
Thanks again,
Rod


#8

That’s great. The model was set out really well, so that’s a great start. Because of this when I saw the video it was quite easy to see what was wrong.

To effective setup of the model is the key so you’re 90% there.