Forum Discussion
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:
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
3 Replies
- 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.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?
