Forum Discussion
How to create multiple active relationships among tables?
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!
I would highly suggest the 2nd option from CiceroBC
Whilst it might seem a good idea for one big flat table, the Power BI engine is designed to work through relationships.
Also if you ever need to add a column and you got one flat table that means you need to reload all the data, as well as bloating the size of your data model, which could lead to performance issues.