Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MaxW
Advocate II
Advocate II

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
GilbertQ
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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.