cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SPMoore
Advocate I
Advocate I

Bi-Directional Relationships - good or bad?

Hi everyone

 

I attended the London Power BI users group recently where Alberto Ferrari was speaking about time intelligence (excellent presentation BTW!).  I got the impression that he seemed quite sceptical of bi-directional relationships in a Power BI data model so I was just wondering about the reasons behind this and want to find out a bit more.  

 

More specifically, in terms of optimisation, will my DAX code execute significantly faster in a single direction environment or are there other reasons that need to be considered?

 

Many thanks

Simon 

3 ACCEPTED SOLUTIONS
scottsen
Memorable Member
Memorable Member

First let me say that... if Alberto says it... it's true.  You will be very hard pressed to find somebody more knowledgable.  We can probabaly tag @marcorusso for comments from their team though.

 

I don't think there are any really interesting performance concerns.  I'm sure we can dream up a weird example, but that is not the general issue.

 

For me... I hate them (bi-directional relationships).  I have had several client consults and several forum questions answered where the answer was just "oh, turn off that stupid bi-directional filter".  

 

It is theoretically supposed to be an ease-of-use feature, but I think it's more of a "ease of confusion" feature.  In fairness, myself (rob collie, marco/alberto, chris webb, ken puls, matt allington, etc) have all trained ourselves to really understand, accept and train people on thinking of the filters working in just 1 direction.  There were some subtlies in dealing with certain models (say, dealing with many to many relationships)... but they weren't THAT big of a deal.

 

I'm not necessarily against the existence of bi-directional relationships -- but I would prefer the default to always be one-way.  I think it would be easier to understand.

 

It is most useful for people that don't even WANT/need to understand how relationships "work" and just stumble into a working solution.  I prefer to understand things 🙂

View solution in original post

ankitpatira
Community Champion
Community Champion

@SPMoore One reason for using bi-directional relation is to have both the charts filtered based on selection on the one ie if you have a relation that is from product table to location table (1 to many) and product table is going to be slicer and location column chart then it doesn't make sense to use bi-directional since you will be selecting something in slicer first and expect column chart to be filtered. But if you're going to use say column chart for both then it makes sense to have bi-directional relationship. So it also depends on your requirements.

View solution in original post

SPMoore,

 

In addition to the suggestion above, there is an article talking about managing relationship in Power BI desktop:

Create and manage relationships in Power BI Desktop

Which should be helpful.

Regards

View solution in original post

6 REPLIES 6
ankitpatira
Community Champion
Community Champion

@SPMoore One reason for using bi-directional relation is to have both the charts filtered based on selection on the one ie if you have a relation that is from product table to location table (1 to many) and product table is going to be slicer and location column chart then it doesn't make sense to use bi-directional since you will be selecting something in slicer first and expect column chart to be filtered. But if you're going to use say column chart for both then it makes sense to have bi-directional relationship. So it also depends on your requirements.

View solution in original post

almost 5 years after this reply and I'm using the bi-directional filter to solve this specific situation, they talk a lot to use Star Schema, but this situation has been true often, here's an example on how I solved the dynamically axis and measures can work on bi-directional relationships.

 

React App (andremaciel66.github.io)

 

datak1ng_0-1617968509867.pngdatak1ng_1-1617968592178.png

 

scottsen
Memorable Member
Memorable Member

First let me say that... if Alberto says it... it's true.  You will be very hard pressed to find somebody more knowledgable.  We can probabaly tag @marcorusso for comments from their team though.

 

I don't think there are any really interesting performance concerns.  I'm sure we can dream up a weird example, but that is not the general issue.

 

For me... I hate them (bi-directional relationships).  I have had several client consults and several forum questions answered where the answer was just "oh, turn off that stupid bi-directional filter".  

 

It is theoretically supposed to be an ease-of-use feature, but I think it's more of a "ease of confusion" feature.  In fairness, myself (rob collie, marco/alberto, chris webb, ken puls, matt allington, etc) have all trained ourselves to really understand, accept and train people on thinking of the filters working in just 1 direction.  There were some subtlies in dealing with certain models (say, dealing with many to many relationships)... but they weren't THAT big of a deal.

 

I'm not necessarily against the existence of bi-directional relationships -- but I would prefer the default to always be one-way.  I think it would be easier to understand.

 

It is most useful for people that don't even WANT/need to understand how relationships "work" and just stumble into a working solution.  I prefer to understand things 🙂

View solution in original post

Thanks for the reply @scottsen. I asked the question as I have a data model with quite a few bi-directional relationships so when Alberto mentioned it I got worried and questioned what I had created Smiley Happy!   

 

As @ankitpatira mentioned though, I have mostly used this feature for several slicers all created as calculated tables derived from the main fact table so when I use one of the slicers then I need the other ones to filter down.  So I do want the filtering to propogate through the fact table to all the other slicers. 

 

But I get your point that it is best practice to have one way relationships.

 

Thanks

Simon

 

 

SPMoore,

 

In addition to the suggestion above, there is an article talking about managing relationship in Power BI desktop:

Create and manage relationships in Power BI Desktop

Which should be helpful.

Regards

View solution in original post

Thanks for the link @v-micsh-msft.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.