(In)active relationships

In Power BI, if you are working with large amounts of data it is important to understand the relationship between the data points to analyse and then visualize the resulting graphs and create an accurate report.

Establishing the relationships between the data points in a Power BI data model allows you to connect the visuals. It is possible to create several relationships between two tables. Most of the time, the first created relationship will be considered as the “active” one. The other one(s) will be considered as “inactive.” These active and inactive relationships determine how the visuals will react when the visual is being interacted with.

First base relationship

The active relationship is the default filter propagation path between two model tables. This means that the active relationship will be used in the visual when filtering is needed. The best practice is to create active relationships for the most used filter path.

Power BI relationship links 

It's possible to introduce additional relationship paths, though these relationships must all be configured as inactive.

Inactive relationships can only be made active via DAX functions. It is achieved by using the USERELATIONSHIP DAX function.

Recognizing and making (in)active relationships

Active

A relationship is active when the line between the model tables is solid, which means that the Active checkbox in the Manage relationships window and the checkbox Make this relationship active, in the Edit relationship menu, are checked.

Power BI relationship editing window

Inactive

A relationship is inactive when the line between the model tables is dotted. The checkbox Active in the Manage relationships window and the checkbox Make this relationship active in the Edit relationship menu are unchecked.

Power BI inactive relationship (dotted line)

Be aware that there can only be one active filter propagation path between two model tables. So, if you want to change one relationship from inactive to active, you must first make the active one inactive and then the inactive one active.

Relationship effects

When making the active relationship to inactive so that another relationship becomes the active one, you implicitly change the default filter propagation path.

It is important to note that this could have a big impact on the design of your report.

Spurious correlations

Don’t look for relationships where there aren’t any. Keep your Power BI data models organized and the relationships between them clear using the (in)active status.

If you’re stumped on how to progress in Power BI, sign-up at 5miles for a free two-week trial and explore the 70 different Power BI Challenges that can improve your data modelling process.