cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MaxW
Helper II
Helper 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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!