Why Is My Relationship Inactive in Power BI Desktop?

November 7, 2015 Melissa Coates

Summary

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.

Second method is to drag one column to another in the Relationships pane. This way sets all of the advanced options to its default settings. Since I have very specific preferences for the Advanced options, I tend not to use this alternative as often.

Third method is to use the Autodetect functionality within the Manage Relationships window. This can be a time-saver, but you still need to verify each one is doing what it’s supposed to do. Therefore, I tend to lean towards creating them manually so that I’m certain the data model is specified exactly the way I want it. Since Power BI Desktop does Autodetect by default, you probably won't need to use Autodetect unless you've deleted some relationships along the way.

What are Bi-Directional Relationships in Power BI?

Bi-directional relationships have a few aliases. People refer to it as as BiDi, cross filtering, and/or many-to-many.

When set to “Single,” the dimension (or lookup, from the ‘one’ side of the relationship) can filter the fact (or base table from the ‘many’ side of the relationship), but it cannot filter the other way around. That's why the arrow points one direction. In the following screen shot, this means we can slice and dice the Quantity measure (from the fact) by Customer (from the dimension). For example: Sum of Quantity by Customer.

This works well for most scenarios when a traditional dimensional model (aka star schema) is implemented, wherein dimensions contain attributes and facts contain measures. This facilitates the slicing/dicing in the dimension and aggregation in the fact.

Conversely, bi-directional relationships permit filters to propagate in Both directions (as opposed to one-way with the Single filter). Under the covers, for filtering purposes, Power BI Desktop treats the two tables as if they’ve been flattened into one single table.

With the standard “Single” direction relationship as shown above, we could *not* do something like Count of Customers by Quantity sold – that type of question is moving the aggregation to the dimension and the slicing/dicing to the fact which is the inverse of what we usually do. However, the Both setting allows that type of analysis to work.

Sounds like a great idea. Why wouldn’t we want to always do this?

When to Avoid Using Bi-Directional Filtering in Power BI Desktop

If you have more than one fact table in your dimensional model, you want to stay away from the ‘Both’ setting. I don’t claim to understand entirely how it works under the covers, but I can confirm that you end up with some inactive relationships that you really want to be active. As you probably know, an inactive relationship can only be invoked through a DAX calculation so that limits some self-service BI scenarios.

We have a project with a traditional star schema with 4 fact tables and approximately 15 dimensions (about half are actually role-playing date dimensions). Each relationship from dimension to fact represents a standard 1-to-many. After migrating the existing model from XLSX into Power BI Desktop, I was surprised to see approximately 5 of the relationships were set to inactive in Power BI Desktop. Here is a vastly simplified version of that data model (reduced to 2 dimensions in this example) to illustrate what I was experiencing:

See in the above screen shot how some of the relationships are inactive? That’s because Power BI Desktop detected some ambiguity. Since some of the ‘Both’ cross filter settings were allowing bi-directional relationships, that caused issues when trying to create relationships which otherwise are absolutely valid and active. This Power BI Support article states: “If you have two or more tables that also have lookup tables (with some in common) then you wouldn't want to use the Both setting.” So, my general rule is to avoid using the ‘Both’ setting when any dimension (lookup) tables are shared among multiple fact (base) tables.

Also, be aware that since the bi-directional ‘Both’ setting does alter how filters work, more complex queries may be sent to the data source. So be sure to test for both performance and accuracy, and that row level security does what you expect it to do. The kind of testing we always do anyway, right? Right.

In conclusion, I would like to propose that bi-directional cross filtering should be used in moderation – If you have a specific analytical need, and if there is one single fact table sitting in the center of dimension (lookup) tables then it should be just fine to use. If you see some unexpected behavior, or you see inactive relationships that you don't expect, the relationship settings are one area to check.

Finding More Information

Power BI Support: Create and Manage Relationships in Power BI Desktop

Chris Webb Blog: Bi-Directional Relationships and Many-to-Many in the Power BI Designer

and One-to-One Relationships in Power BI