How to create multiple relationships between two tables (on separate columns)

[Anonymous]

Hello,

I'm working on a report with multiple tables and multiple relationships between the tables. Want to know if it's possible to realize the following scenario.

Let's say there is a table A, and a table B. Both tables have the Category and Date columns. A relationship is already created between these two tables on Category column, and a Category slicer is used to filter both tables.

Now I want to have another slicer based on the Date column, also want it to be able to filter both tables. I know it will work if I merge these two tables. But in reality, there is a lot of data and different tables are responsible for different areas.

Visuals:

Data tables:

Please let me know if you have any ideas.

Thank you!

[v-yingjl]

Hi @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:

1. Relate many-to-many dimensions guidance 
2. many-many relationships in Power BI Desktop 

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.

[amitchandak]

@Anonymous , In this case, you have to create a common date, category, and group tables.  category and group may be on one table.

Refer to this file how to create a common dimension from two tables 

https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

Also, refer

https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/

https://www.seerinteractive.com/blog/join-many-many-power-bi/

[Ashish_Mathur · Accepted solution]

Hi,

If you do not want to append the 2 tables then do this:

1. Remove the relationship between the 2 tables
2. Create a Category table and build a relationship from the Category columns of both tables to the category column of the new table
3. Create a Calendar table and build relationships as mentioned in point 2 above

Hope this helps.