Many to many relation between fact and dimension tables

[Lullabit13]

Hi! 
What is the best way to eliminate many-to-many relationships between a fact table and a dimension table?
For example, I have a fact table (Sales) related to a calendar table through the "year_month" field, or related to a customer dimension through the id_customer field, and the relationship between them is many-to-many.
Thanks in advance

[Vijay_Chethan · Accepted solution]

Hello Lullabit13,
Create an intermediate table that connects the fact table and the dimension table. This bridge table will contain unique combinations of keys from both tables, effectively transforming the many-to-many relationship into two one-to-many relationships

if this helps please mark as solution

[v-nmadadi-msft]

Hi @Lullabit13 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @Lullabit13 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @Lullabit13 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[miTutorials]

Here is a detailed explanation of how to manage Many to Many Relationships !!

Power BI Many-to-Many Relationships: Issues and Fixes Explained | MiTutorials - YouTube

[bhanu_gautam · Accepted solution]

@Lullabit13 

Bridge Table: Create a bridge table that contains unique combinations of the keys from both tables. This bridge table will act as an intermediary to resolve the many-to-many relationship.

Aggregation: Aggregate the fact table data to a level that matches the granularity of the dimension table. This can help in reducing the many-to-many relationship to a one-to-many relationship.

Data Modeling: Redesign your data model to ensure that the relationships are one-to-many. This might involve creating additional dimension tables or restructuring your existing tables.

[Vijay_Chethan · Accepted solution]

Hello Lullabit13,
Create an intermediate table that connects the fact table and the dimension table. This bridge table will contain unique combinations of keys from both tables, effectively transforming the many-to-many relationship into two one-to-many relationships

if this helps please mark as solution

[Lullabit13]

Hello, 
If I create a bridge table with id, sales_id, and customer_id, how should I define the cardinality and filter direction?

[Vijay_Chethan]

Based on your requirement , you can keep direction single sided but you want filtering to be done in other tables based on filter on one, then switch to both directions, cardinality will end up as 1 to many