How to create multiple active relationships among tables?

[Anonymous]

Hi,

Good Morning everyone! Thanks for your help in advance!

So I m trying to do an analysis of a data and I have created active relationship based on the activity owner. However, each activity owner currently overseas 7-8 countries hence I wanted a next level slicer on country. However, since only one active relationship (owner) exists between 4 of my tables, when I select the owner in the slicer all the data gets updated in the dashboard. However, when I further (additionally) select the country, the data does not get updated as it is not an active relationship. Please help as I want to use 2 active relationships for my data across 4 tables.

Thanks

[CiceroBC]

@Anonymous,
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]

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 displayed

[Anonymous]

Hi,

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!

[GilbertQ]

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.

[Anonymous]

u might want to use  USERELATIONSHIP function in DAX...

[Anonymous]

As per my understanding, Userrelationship function can be used on numeric values. Unfortunately both my fields are non-numeric. Do you have any other workaround ?