Forum Discussion
How to create multiple relationships between two tables (on separate columns)
Hi,
If you do not want to append the 2 tables then do this:
- Remove the relationship between the 2 tables
- Create a Category table and build a relationship from the Category columns of both tables to the category column of the new table
- Create a Calendar table and build relationships as mentioned in point 2 above
Hope this helps.
Community SupportHi Anonymous ,
Actually we cannot create multi relationships between two tables directly, and we don't recommend relating two fact-type tables directly using many-to-many cardinality. The main reason is because the model won't provide flexibility in the ways you report visuals filter or group.
When you have a many-to-many relationship between dimension-type tables, we provide the following guidance:
- Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
- Add a bridging table to store associated entities
- Create one-to-many relationships between the three tables
- Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
- Hide the bridging table (unless it contains additional columns or measures required for reporting)
In this case, you can create two bridge tables that one has [Category] field and the other has [Date] field, create one-to-many relationships between your fact table and set the filter directions as both.
These documents explain many-to-many relationships in details that you can refer:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.