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

[MaxW]

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:

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

Thank you in advance,

Max

[GilbertQ]

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.

[MaxW]

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.

[GilbertQ]

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?