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

[RodWhiteley]

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

Report layout

Schematic of the data model

[MFelix]

Hi @RodWhiteley,

Believe that you problem is related with the the way you have the interactions between your visuals, since you have the Slicer linked to Therapist table and this one is related with the doctor when you filter out the therapist your final table will only show the therapist you selected.

If you make the interactions between your slicer and the visit visual active and inactive to the doctor visual this should give what you need since you will be selecting the therapist on the slicer not affecting the Doctors visual and then when you select a line in the table it will give you the doctors related.

Not sure if my data is OK when compared to your data based since from your image is hard to understand if one MRN can have more than one therapist and if on the doctorsvisit table the therapist is related with MRN can have more than one therapist.

Regards,

MFelix

[RodWhiteley · Accepted solution]

Thanks for your help on this Felix - this issue ultimately was that I was filtering using the wrong value as a slicer (as you probably guessed).

The GUID for each patient is in the therapist Appointments table, the doctor appointments table, and as a dimension table (being a DISTINCT UNION of this same column in both these tables)

I had been stupidly taking the GUID from the Therapist appointment table in the visualisation, and not from the dimension table (which is why I had made it in the first place!)

Reminds me of a quote:

"You can't make things idiot-proof because idiots are so ingenious"

Thanks again for your help and work on this