cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MaxW Regular Visitor
Regular Visitor

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

Hi,

 

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:

 

IncidentHazardError.PNG

 

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,

Max

3 REPLIES 3
Super User
Super User

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

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 Datanaut!"
MaxW Regular Visitor
Regular Visitor

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

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.

Highlighted
Super User
Super User

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

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 Datanaut!"