cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to create multiple active relationships among tables?

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

7 REPLIES 7

Re: How to create multiple active relationships among tables?

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

CiceroBC Regular Visitor
Regular Visitor

Re: How to create multiple active relationships among tables?

@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!

Super User IV
Super User IV

Re: How to create multiple active relationships among tables?

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Anonymous
Not applicable

Re: How to create multiple active relationships among tables?

Hi,

Could you please explain how can we add country as a dimension ? I dont know how to execute it.

Anonymous
Not applicable

Re: How to create multiple active relationships among tables?

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 ?

Highlighted
CiceroBC Regular Visitor
Regular Visitor

Re: 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!

Super User IV
Super User IV

Re: How to create multiple active relationships among tables?

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors