Why doesnt PowerBI allow multiple active relationships


The Power BI Desktop tool now attempts to utilize bi-directional relationships when it can. If any ambiguity exists, some of your relationships may become inactive. If you are seeing inactive relationships (the dotted line), take a look in Advanced properties to see if the “cross filter direction” is set to “Both” rather than “Single” for some of the relationships. This is particularly true if you have multiple fact tables in your dataset – it’s recommended to stay away from bi-directional relationships if dimension (lookup) tables are shared across fact tables. Although the “cross filter direction” property allows many-to-many relationships and offers more flexibility with respect to filter propagation, my approach is to use bi-directional filtering sparingly and only when a specific analytical need has been identified.

How Power BI Desktop Handles Relationships

After doing some experimentation, reading, and talking with people, I've learned these important things:

  • Power BI Desktop will auto-detect relationships when it can (ex: matching column names and data types). According to this Power BI Support article:  “Cardinality, Cross filter direction, and Active properties are automatically set.”
  • Power BI Desktop won’t necessarily create all relationships. If it doesn’t have a very high confidence what to do, it won’t create the relationship.
  • Power BI Desktop will attempt to set the cross filter direction to “Both” whenever it can – that’s the new default behavior.
  • Power BI Desktop may set a relationship to inactive if there is some ambiguity present.

So, be sure review all of the relationships in your dataset, and don’t rely entirely on the auto-detect functionality. 

How to Create Relationships in Power BI Desktop

There are three ways to create relationships in Power BI Desktop:

My preferred method for creating a relationship is to use the “Manage Relationships” pane. This allows me to specify the column from each table, as well as the items under Advanced options including cardinality, cross filter direction, and if it’s active or not.