Forum Discussion
How to create multiple active relationships among tables?
Advocate IIAnonymous,
This might be a scenario where changing your table relationships to use "Many to Many" cardinality could work. Can you dictate the cross-filter direction, so the selection of a country only filters one side of the relationship? Not sure if that would work with your table structure, but it might be an option.
Good luck!
- GilbertQ
Super User
Hi there
What I would suggest is to remodel your data so country could be a dimension in your data model. This will allow you to cross filter automatically on both the owner and or country. That is best practise in terms of performance.
This will also mean that the DAX is a lot easier.
Both bi-directional cross filter directions and many to many should be looked at very carefully as it can result in the wrong values being displayedHi,
Could you please explain how can we add country as a dimension ? I dont know how to execute it.
- CiceroBC
Anonymous,
I think what GilbertQ is trying to say is to build another table (or potentially a view) of just the countries and activity owner associations, so if you previously had:
Table 1
Activity Owner
Country
Other Fields
Table 2
Activity Owner
Other Fields
The reorganization would be to take the Country from Table 1 and make a dimension table with only Activity Owner associations to Country, then your slicers could be on the Country to limit the Activity Owners without affecting the other data. So, you would have something like (obviously not verbatim, but hopefully you get the idea):
New Dimension Table
Country
Activity Owner reference
GilbertQ is absolutely right, this type of normalization is best practice. If you have enough control over your data source to do so, you totally should. Also, in reference to making the relationships play by their own rules (cardinality), it can definitely lead to some weird filtering.
At the risk of throwing a weird third option into the mix, your statement that you have 4 tables all linked together by the activity owner got me thinking...instead of messing with the relationships, would it be easier (albeit a lot less efficient) to just merge your tables together in the Power Query Editor (Home --> Merge Queries)? It might be a huge table in the end, but if all of your records have an activity owner as a common element to merge on, you could theoretically then slice on any field you like.
Just a thought. Good luck!
