Power BI Deactivated Your Relationship (And Didn't Tell You)

TL;DR

• A dotted line in the model = inactive relationship
• Power BI deactivates relationships automatically to avoid ambiguity
• Use USERELATIONSHIP() to activate it in specific measures
• Most common pattern: multiple dates against a Calendar table
• Alternative: role-playing dimensions (duplicate the table)

---

The Symptom

You have a dashboard working perfectly. One day, a visual stops filtering correctly. You select a value in a slicer and… the visual shows all the data. Or worse: zero.

You check the measures. They’re fine. You check the visual filters. They’re fine. You check the data. It exists.

You’re going crazy. Then you go to Model view and… wait, why is that line dotted?

---

Why Power BI Deactivates Relationships

The Golden Rule: One Active Path

Between two tables, Power BI only allows one active path of relationships. When you create a relationship that creates ambiguity (there’s more than one possible path), Power BI automatically deactivates one of them.

It doesn’t warn you with a popup. Doesn’t send you an email. Doesn’t show a warning. It just makes the line dotted and moves on like nothing happened.

Months later, when a user tells you “this doesn’t work,” you realize you’ve had a dead relationship for weeks.

When It Happens (Typical Cases)

1. Multiple dates in the same table

Orders[OrderDate] → Calendar
Orders[ShipDate]  → Calendar (INACTIVE)
Orders[PayDate]   → Calendar (INACTIVE)

2. Bridge tables with multiple connections

Customers → Sales
Customers → Returns → Sales (creates cycle, gets deactivated)

3. Bidirectional relationships that create cycles

When you enable “filter in both directions” and it creates a loop with another existing relationship.

---

How to Detect Inactive Relationships

Visual Method

1. Go to Model view (diagram icon)
2. Look for dotted lines (not solid)
3. Click on the line to see details

Systematic Method

1. Right-click any relationship
2. “Manage relationships”
3. The “Active” column shows the status

Indicator	Meaning
Solid line	Active relationship (used by default)
Dotted line	Inactive relationship (ignored unless you activate it)

---

The Solution: USERELATIONSHIP

You don’t need to activate the relationship permanently (that can create other ambiguity problems). You can activate it only in measures that need it:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        Orders[ShipDate],
        Calendar[Date]
    )
)

How It Works

USERELATIONSHIP does three things:

1. Deactivates temporarily the active relationship between those tables
2. Activates the specified inactive relationship
3. Calculates the measure with that new context
4. Restores everything to the original state

It only affects that measure’s calculation. The rest of the model doesn’t notice.

Syntax

USERELATIONSHIP(
    source_column,      -- The column from the fact table
    destination_column  -- The column from the dimension table
)

Important: You must specify the exact columns of the inactive relationship, in the correct order.

---

Complete Example: Orders with Multiple Dates

The Scenario

Orders table:

OrderID	OrderDate	ShipDate	PayDate	Amount
1	2025-01-15	2025-01-20	2025-01-18	100
2	2025-01-16	2025-01-25	null	200

Calendar table (single):

Date	Month	Quarter	Year
2025-01-15	January	Q1	2025
…	…	…	…

The Relationships

• Orders[OrderDate] → Calendar[Date] (ACTIVE)
• Orders[ShipDate] → Calendar[Date] (inactive)
• Orders[PayDate] → Calendar[Date] (inactive)

The Measures

// Sales by order date (uses active relationship, normal)
Sales Ordered = SUM(Orders[Amount])

// Sales by ship date (activates inactive relationship)
Sales Shipped =
CALCULATE(
    SUM(Orders[Amount]),
    USERELATIONSHIP(Orders[ShipDate], Calendar[Date])
)

// Sales by pay date
Sales Collected =
CALCULATE(
    SUM(Orders[Amount]),
    USERELATIONSHIP(Orders[PayDate], Calendar[Date])
)

The Result

Now you can use the same date slicer to analyze:

• When orders were placed
• When they were shipped
• When they were paid

Three perspectives, one Calendar table, zero ambiguity.

---

Alternative Pattern: Role-Playing Dimensions

If USERELATIONSHIP complicates your model (many measures, many dates), consider duplicating the dimension table:

How to Implement It

// In Power Query, create references from the Calendar table
Calendar_Order = Calendar
Calendar_Ship = Calendar
Calendar_Pay = Calendar

Now you have:

• Orders[OrderDate] → Calendar_Order[Date] (active)
• Orders[ShipDate] → Calendar_Ship[Date] (active)
• Orders[PayDate] → Calendar_Pay[Date] (active)

Advantages

• No need for USERELATIONSHIP in every measure
• Independent slicers per date type
• More intuitive for business users

Disadvantages

• Larger model (duplicated tables)
• More maintenance if you change the original table
• Can confuse if not well documented

When to Use Each Approach

Situation	Recommendation
2-3 dates, few measures	USERELATIONSHIP
Many dates, many measures	Role-playing dimensions
Technical users	USERELATIONSHIP
Business users creating their own reports	Role-playing dimensions

---

Common Mistakes

1. Specifying Columns in Wrong Order

// ❌ WRONG: incorrect order
USERELATIONSHIP(Calendar[Date], Orders[ShipDate])

// ✅ RIGHT: fact column first, then dimension column
USERELATIONSHIP(Orders[ShipDate], Calendar[Date])

2. Forgetting CALCULATE

// ❌ WRONG: USERELATIONSHIP without CALCULATE
Sales Shipped =
SUM(Orders[Amount]) + USERELATIONSHIP(...)

// ✅ RIGHT: always inside CALCULATE
Sales Shipped =
CALCULATE(
    SUM(Orders[Amount]),
    USERELATIONSHIP(Orders[ShipDate], Calendar[Date])
)

3. Activating a Relationship That Doesn’t Exist

If you specify columns that have no relationship (neither active nor inactive), DAX will error. The relationship must exist in the model.

4. Not Testing with Real Data

The measure may look correct but give unexpected results if:

• There are nulls in the date column
• The relationship has incorrect cardinality
• There are report/page filters interfering

---

Troubleshooting Checklist

When a visual doesn’t filter and you suspect relationships:

1. Verify the Model

• Are there dotted lines you didn’t expect?
• Does the relationship you need exist?
• Is it active or inactive?

2. Verify the Measure

• Does it use USERELATIONSHIP if the relationship is inactive?
• Is it inside CALCULATE?
• Are the columns in the correct order?

3. Verify the Data

• Are there values in the date column?
• Do dates match between tables? (same format, no nulls)
• Is cardinality correct? (1:N, not N:N)

4. Verify the Context

• Are there report filters interfering?
• Is the slicer connected to the correct table?
• Are there other measures modifying the context?

---

Conclusion

Inactive relationships aren’t a bug, they’re a feature. Power BI deactivates them to protect you from ambiguous results.

The problem is it does it silently. That’s why:

1. Review the model regularly looking for dotted lines
2. Document why certain relationships are inactive
3. Use USERELATIONSHIP when you need to activate them
4. Consider role-playing dimensions if the model gets complicated

And next time a visual “stops working”… you know where to look first.

---