Filters in a single table report
I have a single chart on a report driven by a single data table called "visits." The table contains rows with visit type, doctor, patient, and date. Visits may be sick visits or well visits. I am using color by sick/well to see how many sick and well visit a doctor has in a month. I have two filters, one for doctor, and one for patient. A patient will only have one doctor. A doctor may have many patients. When I select the doctor from the doctor filter, I want the patient filter box to show only those patients that have visited that doctor.
Visit type / doctor / patient / date
sick / Dr A / John / Jan-2017
sick / Dr A / Ted / Jan-2017
well / Dr X / Bob / Jan 2017
When I filter by doctor A, I only want to see the patient filter populate with John and Ted. Instead, it is showing John, Ted, and Bob (i.e. it is not limiting the second filter based on the selection in the first filter).
I've spent days trying to figure this out. It can't be that difficult, can it?