How to Use Inactive Relationship in Power BI

When you connect tables in Power BI, you can only set one relationship between them as "active." This can feel limiting, especially when you need to analyze your data through different contexts, like viewing sales by order date and then by ship date. This article will show you exactly how to use inactive relationships in your data model, giving you the flexibility you need for more insightful reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Are Active vs. Inactive Relationships in Power BI?

In Power BI, relationships set up the pathways for filters to flow between tables. Think of your data model as a map with cities (tables) and roads (relationships) connecting them. To get from a date filter to your sales numbers, Power BI needs a clear path to follow.

Active Relationships: The Main Highway

An active relationship is the default, primary route that Power BI uses for calculations and filtering. It's represented by a solid line in the model view.

For example, if you connect a Sales table to a DimDate (Calendar) table, you'd likely create an active relationship between Sales[OrderDate] and DimDate[Date]. When you build a chart showing sales by month, Power BI uses this active "highway" to calculate the correct sales total for each time period.

However, Power BI has a hard rule: there can be only one active relationship between any two tables. This is to prevent ambiguity. If you had two active relationships - say, one for Order Date and one for Ship Date - Power BI wouldn’t know which one to use for calculations. Should "January Sales" be calculated based on orders placed in January or shipped in January? The single active relationship rule eliminates this confusion.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Inactive Relationships: The Scenic Side Road

An inactive relationship is a secondary connection between two tables that exists in your model but is dormant by default. It's represented by a dotted line in the model view.

Following our example, you could create a second relationship between Sales[ShipDate] and DimDate[Date]. Because the active relationship for OrderDate already exists, Power BI automatically sets this new ShipDate relationship to inactive. It won't be used for standard calculations, but it’s available and waiting for when you specifically call on it. This "side road" allows you to perform calculations based on a different context without creating confusing model configurations.

Using inactive relationships is the clean, industry-standard way to manage multiple date (or other) connections to a single dimension table - a scenario often referred to as a "role-playing dimension."

When Should You Use an Inactive Relationship? A Common Scenario

The most classic use case for an inactive relationship involves dates. Imagine you manage an e-commerce store and want to analyze your sales data. Your Sales table contains key date information for every transaction:

• OrderDate: The day the customer placed the order.
• ShipDate: The day the order was shipped from the warehouse.
• DeliveryDate: The day the package arrived at the customer's location.

You also have a standard calendar table, DimDate, with columns for Date, MonthName, Quarter, and Year. To analyze performance, you need to answer questions based on different date contexts:

• How many sales did we get ordered last quarter?
• How much revenue did we recognize for products that shipped last quarter?
• How many orders were delivered each week?

To do this, you need to connect your DimDate table to the Sales table using all three date columns. In the Power BI model view, you'd set this up as follows:

1. Drag DimDate[Date] onto Sales[OrderDate] to create the active relationship (solid line). This is your primary analysis path.
2. Drag DimDate[Date] onto Sales[ShipDate]. Power BI will automatically create this as an inactive relationship (dotted line).
3. Drag DimDate[Date] onto Sales[DeliveryDate]. This will also be created as an inactive relationship (dotted line).

Your model now has one main path for calculations (OrderDate) and two alternates (ShipDate, DeliveryDate) ready to be activated on command.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Meet Your New Best Friend: The USERELATIONSHIP Function

So, how do you actually use these dormant, dotted-line relationships? The answer is a powerful DAX function called USERELATIONSHIP.

On its own, USERELATIONSHIP doesn’t do much. Its sole purpose is to tell another function - almost always CALCULATE - to temporarily ignore the active relationship and use a specific inactive one instead for a single calculation. It’s like telling your GPS to take that scenic side road for just one part of your journey.

The Syntax

The structure of the function is straightforward:

USERELATIONSHIP(<columnName1>, <columnName2>)

• <columnName1>: Usually the column on the "many" side of the relationship (e.g., Sales[ShipDate]).
• <columnName2>: The column on the "one" side of the relationship (e.g., DimDate[Date]).

Important Note: The function argument order (which column comes first) can be flexible, but the relationship between these two columns must already exist in your data model. USERELATIONSHIP activates a pre-built path, it doesn't create one on the fly.

Step-by-Step Example: Calculating Sales by Both Order Date and Ship Date

Let's walk through our e-commerce scenario to see this in practice. You've already set up your relationships in the model view: Sales[OrderDate] is active, and Sales[ShipDate] is inactive.

Step 1: Create a Base Measure for Total Sales

First, create a standard measure for total sales. This measure will automatically use the active relationship (Sales[OrderDate]), so it represents sales based on when the order was placed.

Total Sales by Order Date = SUM(Sales[SalesAmount])

This is a simple, explicit measure. Whenever you drop this into a visual sliced by date, it will show you sales that were ordered in that time period.

Step 2: Create a New Measure Using USERELATIONSHIP

Next, you’ll create your new measure for sales by ship date. This is where CALCULATE and USERELATIONSHIP work together.

Total Sales by Ship Date = CALCULATE( SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], DimDate[Date]) )

Let’s break this down:

• CALCULATE(...): Modifies the context of a calculation.
• SUM(Sales[SalesAmount]): The expression to evaluate.
• USERELATIONSHIP(Sales[ShipDate], DimDate[Date]): This filter tells CALCULATE to use the ShipDate relationship instead of the active OrderDate one.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Add Both Measures to a Visual

Now for the payoff. Go to your report view and create a table or a line chart.

1. From your DimDate table, drag MonthName onto the axis or into the first column of your table.
2. From your measures table, drag in both Total Sales by Order Date and Total Sales by Ship Date.

Instantly, you'll see a comparison of your sales activity from two different perspectives against the same time axis. In January, you might see $100,000 in sales ordered but only $85,000 in sales shipped, because some of those January orders won't ship until February. This single visual delivers powerful insights into your business operations - insights that would be impossible without activating that inactive relationship.

Tips and Best Practices for Using Inactive Relationships

To keep your reports clean and efficient, follow these simple guidelines:

• Use Descriptive Measure Names: Your measure names should make it immediately clear which relationship is being used. Naming them "Sales by Order Date" and "Sales by Ship Date" is much better than "Total Sales" and "Total Sales 2," which will only cause confusion for you and your stakeholders.
• Stick to the Standard (Avoid Duplicate Tables): An alternative (and much messier) approach is to duplicate your dimension tables. You could create a DimShipDate table and a DimOrderDate table. This creates a bloated data model, makes managing slicers difficult, and is not a professional or scalable solution. USERELATIONSHIP is the preferred best practice.
• Check Your Model: USERELATIONSHIP will throw an error if a relationship between the two specified columns doesn't already exist in your model view. Always confirm that you have a path (even a dotted one) between the columns you're referencing in your DAX formula.
• Understand Performance Implications: In massive, complex data models with billions of rows, calculations using USERELATIONSHIP might be a fraction slower than those relying on the active relationship. However, for 99% of business scenarios, the performance difference is negligible and by far the best way to achieve this kind of flexible analysis.

Final Thoughts

Inactive relationships are an essential tool for any Power BI user looking to build flexible and insightful business reports. By pairing them with the USERELATIONSHIP function inside CALCULATE, you can analyze your central fact tables against any number of relevant dimensions - like different kinds of dates - all within the same visual and controlled by the same set of filters.

Creating these kinds of advanced models and writing precise DAX formulas can take time and practice. Instead of spending hours in Power BI’s Model View and DAX editor, we built Graphed to simplify the entire process. Just connect your data sources, and then ask questions in plain English like, "Compare total sales by order date vs. ship date for last year as a line chart." We handle understanding the data relationships for you and instantly build a live, interactive dashboard, turning hours of tedious BI work into a 30-second conversation.