Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Help with Relationship Modelling using a shared dimension and many-to-many bridge.



I need some advice with this data model. Maybe this approach is flawed but is there a way to make this (simplified) structure work? I can't get all the relationships at the same time due to ambiguity:




My desired logic:

  • Locations always exist.
  • Locations can have incidents and hazards, both or neither.
  • Incidents can involve zero or many hazards.
  • Hazards can have zero or many incidents.


Example use cases might be:

  • Location with most incidents
  • Hazards with most incidents
  • Incidents without recorded Hazards
  • Lists of Hazards and Incidents by location



Hazard Incident Relationship Schematic.PNG


Thank you in advance,


Super User
Super User

What I would do personally is to merge the Hazards and IncidentHazards tables together using the HazardID.

Then when you model your data you can then create a relationship from Hazards to Incidents based on the IncidentID.
And from there you can then create the required DAX calculations.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Hi Guavaq,

Thank you for the suggestion, but this would create many duplicates in the Hazard table which makes any analysis on that table much more complicated. The other way I considered Is having two Location tables, but that has problems too, especially as I want to use it for RLS.


This dataset is getting published to users who are report builders rather than data modellers and they will just get frustrated with duplicates and/or having to filter location in two places.

Hi there, ok that makes sense to try and keep it as simple as possible for the report builders.

I am sure that there would be a way to model it so that it is easier and simple for them to create their reports?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!