How to set up relationships in your Power BI dashboard
(In)active relationships
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 Power BI dashboard.
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.
Want to learn more about Power BI. Read the following articles:
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 in your Power BI dashboard.
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.
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.
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 Power BI dashboard.
Spurious correlations
Don’t look for relationships where there aren’t any. Keep your Power BI dashboards 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.