Any suggestions on how to build the data model for these kind of tables?

[selected3000]

.....delete

[v-karpurapud]

Hi @selected3000 
Thank you for reaching out to the Microsoft Fabric Community Forum.

The main issue is due to the one-to-many relationship between Location_ID in the Location table and the Location_Details table. While Location_ID is unique in the Location table, it appears multiple times in Location_Details, representing different attributes or records for each location. Merging these tables directly may create duplicate rows and inaccurate aggregations, which can affect the accuracy of your Power BI data model and visuals.

To resolve this, it is recommended to use a bridge table with unique Location_ID values, as suggested by @bhanu_gautam . This bridge table connects the Location and Location_Details tables, allowing you to set up a one-to-many relationship from Location to the bridge, and a many-to-one relationship from Location_Details to the bridge. This structure helps maintain accurate filtering and avoids redundancy.In summary, using a bridge table helps ensure data integrity, supports reliable reporting, and lays the groundwork for future Power BI improvements.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[burakkaragoz]

Hi @selected3000 ,

You're asking all the right questions — and you're spot on about the merge causing duplication. Since Location_Details has multiple rows per Location_ID, merging it directly with Location would definitely break your model.

Bhanu’s bridge table suggestion is solid, and here’s a bit more context:

Why a bridge table works:

• It acts as a neutral connector between Location and Location_Details
• Prevents one-to-many-to-many relationships, which can cause ambiguity in visuals
• Keeps your model clean and avoids duplicate rows in your fact table joins

How to build it (quick version):

Bridge_Location = DISTINCT(Location[Location_ID])

Then:

• Location[Location_ID] → Bridge_Location[Location_ID] (one-to-many)
• Bridge_Location[Location_ID] → Location_Details[Location_ID] (one-to-many)

Now you can use Bridge_Location in slicers or filters, and it’ll propagate cleanly to both sides.

---

Alternative (if you only need one detail per location):

If you only care about the latest or most relevant row from Location_Details, you could:

• Create a summarized table using GROUPBY or TOPN to keep just one row per Location_ID
• Then merge that with Location safely

But if you need all the detail rows, stick with the bridge table — it’s the cleanest way.

Let me know if you want help setting up the relationships or writing the DAX — happy to help.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

---

This response was supported by AI for translation and text editing.

[selected3000]

ChatGPT should be forbidden and you should be permanently banned from this forum. 

[bhanu_gautam · Accepted solution]

@selected3000 Create a bridge table

The bridge table should contain unique Location_ID values. You can create this table using DAX or Power Query.

Bridge_Location = DISTINCT(Location[Location_ID])

Location to Bridge_Location: Create a one-to-many relationship from Location[Location_ID] to Bridge_Location[Location_ID].
Bridge_Location to Location_Details: Create a one-to-many relationship from Bridge_Location[Location_ID] to Location_Details[Location_ID].

Now, you can use the Bridge_Location table to filter and aggregate data across Location and Location_Details without causing duplication issues.