Blog

You are currently viewing Active and Inactive Relationship in PowerBI

Active and Inactive Relationship in PowerBI

Loading

In this blog you will learn about the Active and Inactive relationship in PowerBI. It is a powerful business analytics tool that allows users to visualize data and share insights across their organization. One of the core functionalities of Power BI is the ability to create and manage relationships between tables in a data model. These relationships form the backbone of how data from different tables is connected and interacts within reports and dashboards.

Relationships in Power BI are critical to creating meaningful insights because they allow you to combine data from different sources and perform calculations across multiple tables. Relationships in Power BI can be active or passive, and understanding the difference between these two types is essential to building flexible and powerful data models.

Active Relationship

The active relationship in Power BI is the primary relationship that Power BI uses by default when performing calculations or creating visualizations. When you create a relationship between two tables, Power BI automatically activates one of these relationships. This active relationship is represented by a solid line between the tables in the model view.

In any scenario where multiple relationships exist between two tables, only one relationship can be active at a time. This active relationship determines how data flows and interacts between the tables during filtering and calculations.

For example, if you have an Orders table with an OrderDate field and a Sales table with a SalesDate field, and you create a relationship between these two fields, this relationship will be active by default. When you create a report showing sales by order date, Power BI will automatically use this active relationship to filter and aggregate the data.

Inactive Relationship

An inactive relationship is a relationship that exists between two tables, but is not used by default when performing calculations or creating visualizations. Inactive relationships are represented by a dashed line between the tables in the model view.

Inactive relationships are not used by default in Power BI calculations, but they can be temporarily activated within a DAX (data analysis expression) formula using the USERELATIONSHIP function. This allows you to dynamically switch between different relationships, providing flexibility in your data model.

For example, in the same Orders and Sales scenario, you may also want to analyze sales by ShipDate. You can create an additional relationship between the ShipDate field in the Orders table and the SalesDate field in the Sales table. This relationship will be inactive by default, but you can activate it in specific calculations or visuals when needed.

Steps to Implement Active and Inactive Relationship in PowerBI

Step 1: Open Power BI Desktop

Step 2: Click on Blank report to establish relationship

Step 3: Click on Get data to select your data sources

Step 4: Click on Model view to establish relationship between two or more tables

Note:

  • Power BI automatically establishes relationships between similarly named columns from different tables.

Step 5: In the following figure, a solid line connects two tables using the same stored value columns, indicating an active relationship.

Note:

  • There is always only one active relationship in a model.
  • If there will be more than one relationship, you will need to activate one relationship manually.

Step 6: Now we’ll establish inactive relationship by dragging ShipDate from Orders table and drop to SalesDate in Sales table.

Step 7: Click on Save button

Note:

  • As you can see, there is not selected Make this relationship active

Step 8: Dotted line showing Inactive relationship using ShipDate and SalesDate

Let’s illustrate, how creating active relationship for visual is helpful.

Step 9: Click on Report view

Step 10: Select Line chart from visualizations and put appropriate values

Step 10: Now, go to Model view

Step 11: Right click on active line and select Properties

Step 12: Unselect Make this relationship active and click on Save button

Step 13: Now, go back to Report view to see impact on visual

Note:

  • Without active relation, visual has changed

Conclusion

Active and passive relationships in Power BI provide powerful tools for building complex and flexible data models. By understanding how to use these relationships effectively, you can unlock advanced analytical capabilities, allowing you to perform sophisticated analysis without compromising the simplicity and efficiency of your data model.

Active relationships are the default connections that drive most of your report’s interactions and calculations, while passive relationships provide alternative routes for specific scenarios. With careful planning, documentation, and testing, you can leverage both types of relationships to build robust, dynamic reports that meet a variety of business needs.

Whether you’re analyzing sales data across multiple time periods, tracking employee events in HR, or evaluating financial transactions based on different dates, active and passive relationships in Power BI enable you to gain deeper insights and make more informed decisions.

If you like the article and would like to support me, make sure to: