Help with Modeling

[nmcclary]

Hey all,

For our health system, I would like to examine the correlation between marketing visits to a certain health care provider and referrals following that visit. These visits are all part of different "initiatives" of our marketing department.

Here is my current model:

I have two current problems. First, there are multiple initiatives associated with some physicians. For example, an orthopedic surgeon could be listed under "orthopedic" and also under "shoulder arthroscopy" as two different initiatives. I tried splitting up into primary, secondary,  and tertiary initatives but that didn't work. Is there a way to set this up? I'm not sure if it's mathmatically possible due to referrals not being able to be counted twice.

Second, the key measures I'm using are Count of Referrals and Count of Visits. I can currently see all referrals and all visits to respective providers listed under certain initiatives. Our providers are listed as "Referring_to" in the referrals as in the referral was made to them. When I select a specific initiative, the filters correctly drill down to show only those providers under the initiative. What I cannot get to happen, though, is that when an initiative is selected that only the providers who were visited under that initiative to show up. Currently, all providers who have referred to our provider show up, not just the ones that were visited by the marketing team. I know this has to do with the data model and relationships.

Is there a way to fix this?

Thanks in advance.

[jdbuchanan71]

The first thing that jumps out at me is the use of multiple bi-directional filters.  That is almost always going to cause you problems. 

Is there a way instead to organize the initiatives data so you can have a single table with an identifier of Primary, Secondary, Tertiary?

[nmcclary]

Those filters actually aren't "active" in the sense that I'm not using them at all. I should have just deleted them as they don't work, although the filter between the initiative key and primary initiative did help to filter some of the slicers correctly.

I could create a table like that and that is kind of what I tried to do to start with but then I ran into the problem of having "many-to-many" relationships, which from what I have read is not good either.  If I built a table like that and then tried to create a relationship to the "Visit" table by "Initiative" and then tried to create relationship to the "Referral" table by "Provider" then both of those would be set up as many-to-many, correct?