Active vs Inactive Relationships in Power BI 

Power BI is a powerful data visualization tool that allows users to create complex data and interactive reports. When creating reports in Power BI, one of the features provided is to understand how relationships work between the tables. Relationships allow data to be linked between different tables and will assist in creating detailed, insightful reports. Within the Power BI data model, one can have active and inactive relationships, and understanding the differences between them is critical for building a reliable data model accurately and efficiently. In this blog, you’ll learn about active and inactive relationships, how they differ, and see real-world examples in detail.

Table of Contents:

• What are Relationships in Power BI?
• Active Relationship in Power BI
• Inactive Relationship in Power BI
• Difference between Active Relationship and Inactive Relationship in Power BI
• Performance Consideration
• Conclusion

What are Relationships in Power BI?

Before understanding active and inactive relationships, it is important to understand relationships in Power BI. A relationship in Power BI is a connection between two tables that is used to define the data and how they relate to each other. Relationships allow Power BI to use data from different tables to filter, aggregate, and create visuals. Creating a relationship between them helps to generate reports that show sales performance for different categories.

Power BI supports different types of relationships:

• One-to-Many (1:N): One value in the first table is related to multiple values in the second table.
• Many-to-One (N:1): Multiple values in the first table are related to a single value in the second table. Multiple values in the first table relate to a single value in the second table. In Power BI, this is essentially the same as One-to-Many.
• Many-to-Many (N:N): Multiple values in both tables are related to each other.

Power BI for Professionals – Learn, Apply, and Stand Out!

Enroll now and future-proof your data career!

Explore Program

Active Relationship in Power BI

The active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.

Example: We have a sample dataset from Intellipaat with the following two tables:

1. Students Table:

2. Courses Table:

Note: We will make a relationship between the “Students” table and “Courses” table that Power BI will use by creating an active relationship using the “CourseID”.

Step 1: Load the dataset into Power BI

For loading the data, you need to click on Get Data >Text/CSV

Student Table:

The Students table looks like this once we loaded it into Power BI.

Courses Table:

This is how the Courses table looks after loading it in Power BI.

Step 2: Go to Model View

To check the relationship, click on Model View and drag the CourseID field from the Students table and drop it on the Courses table

Explanation: An active relationship is denoted by a straight line, which is used to join two tables.

Step 3: Result

As you can see, Power BI already shows that a relationship has been created between the two tables.

Inactive Relationship in Power BI

An inactive relationship is a secondary relationship activated with the DAX function temporarily whenever there is a need to perform a specific calculation.

Examples of Inactive relationships:

We will consider a third table called “Enrollments” of students of Intellipaat to establish an inactive relationship between the tables..

Enrollments Table:

Step 1: Load the dataset into Power BI

Open Power BI and click on “Get Data”>Text/CSV

This is how the Enrollments table looks after loading it in Power BI.

Step 2: Creating a Date table

Click on “Modelling” tab >” New Table”>Write DAX formula in the formula bar

DateTable = CALENDAR(DATE(2023,1,1), DATE(2023,12,31))

After writing this formula in the formula bar, you have a new table that contains a date.

As you can see, there is no relationship between  Enrollments and DateTable

Step 3: Create the Relationship

We will create a relationship by dragging the CompletionDate field from the Enrollments Table and dropping it on the Date field in the Date Table.

Step 4: Result

Explanation: Here, as you can see in this line, there is an inactive relationship between the Enrollments table and the Date Table attached with the help of a dotted line.

Step 5: Activating Inactive Relationships Using DAX

We will use the USERELATIONSHIP() function to activate inactive relationships.

Total Students Enrolled by Completion Date =
CALCULATE(
    COUNTROWS(Enrollments),
    USERELATIONSHIP(Enrollments[CompletionDate], DateTable[Date])
)

Explanation: Here, USERELATIONSHIP() is used to activate the inactive relationship between CompletionDate in the Enrollments Table and Date in the DateTable.

Step 6: Creating Visualization

Choose the bar chart from the visualization pane and drag the Month field from the Dates table on the axis.

Explanation: This graph is called a bar chart, which is used to show the total number of students who completed their course.

Difference between Active Relationship and Inactive Relationship in Power BI

Performance Consideration

• When working with large models, avoid creating relationships with the USERELATIONSHIP() function because this will slow your calculations down.
• When you keep your model clean and do not create too many active and inactive relationships, the performance of related measures can improve.
• Make sure that the relationship you created does not have loops, which can slow the model’s performance.
• When creating a relationship, your data should be cleaned, and you should have no blanks or duplicates.

Conclusion

In Power BI, working with active and inactive relationships is an important part of building reports correctly and as efficiently as possible. Active relationships are the ones that Power BI uses by default for filtering the reports and calculations. In contrast, inactive relationships are secondary relationships and would normally not be used unless activated using DAX commands such as USERELATIONSHIP(). If you use inactive relationships correctly, you can build complex multi-table models that reveal more about your data.

To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.

Active and Inactive Relationships in Power BI- FAQs