Forum Discussion
Resolver IChanging One Relationship Interrupts another and vice versa
Hi!
I have a problem. I can get one of my relationship between patient id and reporting period correct, but then the medical and pharmacy spend relationship by reporting period disappears and I can't activate it do to indirect and ambiguity. If I disable the patient id and reporting period, I can then get the medical and pharmacy to work, but then my patient count is wrong.
I've attached the visual outputs that my charts show when the relationship between patient id and reporting period work, but what happens with the medical and pharmacy spend page. I've also copied the relationship tree so someone can help me figure this out.
Thank you!
4 Replies
- d_gosbell
Super User
It's hard to answer this with the limited information available. I'm guessing based on the column names that the many to many bi-directional relationships are based on Patient ID and are likely to be the root cause of all your issues. I don't think these are necessary, I think a better structure would be as follows (I'd suggest taking a copy of your pbix file first in case some of my assumptions about your data are incorrect)
One of my core assumptions is that your master_patient table records changes to patients over time so the patient ID is not unique in that table (hence why you have the MASTER_ID table)
If this is correct I'd suggest the following changes
- Firstly delete all the many-to-many bi-directional relationships
- Then change the relationship between MASTER_ID and MASTER_PATIENT to bi-directional
- Then create 2 new relationships between MASTER_ID[Patient ID] 1 --> * MASTER_MEDICAL[Patient ID] and MASTER_ID[Patient ID] 1 --> * MASTER_PHARMACY[Patient ID]
- Then hide all the columns and tables highlighted in yellow as they should never be used in charts/reports
If my assumptions and guesses are correct you should now be able to make all your relationships active (so none of them should have dotted lines) and your existing reports should work (although if you have used any of the yellow highlighted columns you should replace those with the respective fields from MASTER_REPORTING_DATE or MASTER_PATIENT)
- d_gosbell
novotnajk wrote:
I tried this, but with the fields being hidden, the relationships cannot be created.
Whether the fields are hidden or not has no impact on the relationships. It could only be cardinality issues or some other conflicting relationship that could cause issues with creating relationships, but you should get an error message indicating what the issues is.
novotnajk wrote:
The lines stay dotted. Is there a workarond for that?
The lines will stay dotted until you manually go into the properties for the relationship (by right clicking on the line and choosing the properties option) and tick the "make this relationship active" checkbox.
I just built a small test model to double check that the relationship structure will work and Power BI does not appear to have any issues with it and all the relationships are active.
I've attached a copy of the model from the screenshot above so that you can see how all the relationships are linked. Maybe I've made some incorrect assumptions about your existing model.
