Active vs inactive relationships in Power BI - Compared

Introduction

In Power BI, relationships connect tables to work together in reports. Active relationships are used by default in calculations. Inactive relationships exist but do not affect calculations unless you tell Power BI to use them. This helps when you have multiple ways tables relate.

When you have two tables connected by more than one column and want to choose which connection to use in calculations.

When you want to compare sales by order date and ship date using the same tables.

When you want to keep a relationship for filtering but not use it automatically in all visuals.

When you want to create a measure that uses a different relationship than the default one.

When you want to avoid confusion from multiple active relationships causing ambiguous results.

Steps

Step 1: Open

- Model view in Power BI Desktop

You see all tables and their relationships as lines connecting them.

Step 2: Click

- Relationship line between two tables

The relationship properties pane opens on the right side.

Step 3: Look at

- Relationship properties pane

You see a checkbox labeled 'Make this relationship active'.

Step 4: Check or uncheck

- 'Make this relationship active' checkbox

The relationship becomes active (checked) or inactive (unchecked). The line style changes: solid for active, dashed for inactive.

💡 Only one active relationship can exist between two tables at a time.

Step 5: Create a measure using DAX

- Modeling tab > New measure

You can use USERELATIONSHIP() function in the measure to activate an inactive relationship temporarily.

Step 6: Write DAX formula

- Formula bar

The measure calculates using the inactive relationship only when USERELATIONSHIP() is used.

Before vs After

Before

Two tables have two relationships: one active (solid line) and one inactive (dashed line). Visuals use only the active relationship by default.

After

After creating a measure with USERELATIONSHIP(), the measure uses the inactive relationship for its calculation while other visuals still use the active one.

Settings Reference

Make this relationship active

📍 Relationship properties pane in Model view

Controls whether the relationship is used by default in calculations and filtering.

Default: Checked for one relationship between tables, unchecked for others

USERELATIONSHIP() function

📍 DAX formula bar when creating measures

Temporarily activates an inactive relationship for a specific calculation.

Default: Not used unless specified

Common Mistakes

Trying to have two active relationships between the same tables on different columns.

Power BI allows only one active relationship between two tables at a time, causing errors or unexpected results.

Keep one relationship active and set others inactive. Use USERELATIONSHIP() in measures to use inactive ones.

Not using USERELATIONSHIP() when expecting an inactive relationship to affect calculations.

Inactive relationships do not affect calculations unless explicitly activated in DAX.

Use USERELATIONSHIP() inside CALCULATE() to activate the inactive relationship for that measure.

Summary

Active relationships are used by default in Power BI calculations and filtering.

Inactive relationships exist but do not affect calculations unless activated by DAX.

Use USERELATIONSHIP() in measures to temporarily activate inactive relationships.