How to Use Inactive Relationship in Power BI DAX

Ever tried connecting a table with multiple date columns - like an order date and a ship date - to a single calendar table in Power BI, only to be met with a confusing "inactive relationship" dotted line? It’s a common scenario that can feel like a roadblock, but it's actually Power BI's way of avoiding confusion. This guide will show you exactly what inactive relationships are, why they're useful, and how to activate them on command using a simple DAX function to unlock deeper levels of analysis.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Are Inactive Relationships in Power BI?

In Power BI's data model, an active relationship is a solid line connecting two tables. It represents the primary, default path that Power BI uses to filter data. For example, if you connect your Sales table to your Calendar table using the OrderDate field, clicking on a year in a slicer will filter your sales visuals to show sales ordered in that year. This is the main highway for your data.

You can only have one active relationship between any two tables at a time. If you try to create a second relationship - say, connecting the ShipDate field from the same Sales table to the Calendar table - Power BI will create it as an inactive relationship, shown as a dotted line.

Why? To avoid ambiguity. If both relationships were active, Power BI wouldn't know which path to take. If a user filtered by "2023," should it show sales ordered in 2023 or sales shipped in 2023? By forcing one path to be inactive, Power BI avoids guesswork and maintains data integrity, giving you full control over which context to apply in your calculations.

This setup is essential for what's known as a "role-playing dimension," where a single table (like a Calendar table) can serve multiple purposes (describing order dates, ship dates, due dates, etc.).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Setting Up Your Data Model: A Step-by-Step Example

Let's use a classic e-commerce scenario. We have a sales fact table and a calendar dimension table. Visualizing sales performance based on when an order was placed versus when it was shipped is a basic but essential reporting need.

1. Your Sample Data

Imagine you have two simple tables:

• A Sales table with columns like OrderID, OrderDate, ShipDate, and SalesAmount.
• A Calendar table, which is a standard date dimension with a column named Date and other time intelligence columns like Year, Month, and Quarter.

Your goal is to connect both OrderDate and ShipDate to the single Date column in the Calendar table.

2. Create the Relationships

In Power BI, navigate to the Model view. If you haven't already, import your Sales and Calendar tables.

1. Click and drag the OrderDate field from your Sales table onto the Date field in your Calendar table. Power BI will create a solid line, indicating an active one-to-many relationship. This is now your default path.
2. Next, click and drag the ShipDate field from the Sales table onto the same Date field in the Calendar table. You'll notice this second line is dotted. This is your inactive relationship.

Your model now looks something like this: The Calendar table is ready to filter sales data, but by default, it will only do so based on the OrderDate, because that’s the active path.

Activating Inactive Relationships with the USERELATIONSHIP Function

The magic happens with a DAX function called USERELATIONSHIP. This function doesn't permanently change the relationship status, instead, it tells a specific calculation to temporarily use an inactive relationship instead of the active one.

It acts like a detour. While all standard traffic follows the main highway (the active relationship), USERELATIONSHIP allows a specific car (your DAX measure) to take the scenic route (the inactive relationship) to get a different view.

The USERELATIONSHIP function is almost always used inside the CALCULATE function, which modifies the context of a calculation. The syntax looks like this:

CALCULATE ( <expression>, USERELATIONSHIP ( <columnName1>, <columnName2> ) )

• The <expression> is the calculation you want to perform, like SUM(Sales[SalesAmount]).
• USERELATIONSHIP is the instruction to change the path. It takes the two columns that form the endpoints of the inactive relationship as its arguments. The order doesn't typically matter here.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Writing the DAX: Sales by Order Date vs. Ship Date

Now, let’s create two measures to see this in action. The first will use the default active relationship, and the second will leverage our inactive ShipDate relationship.

Measure 1: Total Sales by Order Date (Using the Active Relationship)

This is the easy part. Because the relationship between OrderDate and Calendar[Date] is already active, a standard aggregation measure is all you need. In the Report view, click on New Measure and enter the following DAX formula:

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

When you use this measure in a visual with a date slicer from your Calendar table, it will automatically filter sales based on the order date. For clarity, you can name it Total Sales by Order Date, but even a simple Total Sales = SUM(Sales[SalesAmount]) would work the same way.

Measure 2: Total Sales by Ship Date (Using the Inactive Relationship)

Here is where we use USERELATIONSHIP to create a second perspective on our sales data. Create another new measure with this formula:

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

Breaking it down:

• CALCULATE(...) tells Power BI we are about to change the evaluation context.
• SUM(Sales[SalesAmount]) is the base calculation we want to perform.
• USERELATIONSHIP(Sales[ShipDate], Calendar[Date]) is the filter instruction. It tells CALCULATE to ignore the active Sales[OrderDate] -> Calendar[Date] relationship for this measure only and instead use the inactive Sales[ShipDate] -> Calendar[Date] path.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualizing the Results

You can now build a table or chart visual to compare these two metrics. Drag the Year or Month from your Calendar table onto a visual and add both of the new measures:

• Total Sales by Order Date
• Total Sales by Ship Date

You will see two different sets of numbers side-by-side. Filtering by the year "2023" will now show you the total amount for orders placed in 2023 in one column and the total amount for orders shipped in 2023 in another. You've successfully created two analytical viewpoints from a single, clean data model.

More Real-World Use Cases

This technique is incredibly versatile and applies to many other business scenarios beyond order and ship dates:

• Financial Forecasting: You can have a FactForecast table and a FactSales table, both connected to a DimDate table. You can set the sale date relationship as active and the forecast date relationship as inactive, then use USERELATIONSHIP to easily compare actual vs. forecasted figures over time.
• Project Management: A project tasks table might have StartDate, DueDate, and CompletedDate. Connecting all three to a calendar table allows you to analyze planned vs. actual timelines.
• Inventory Management: Track inventory movements by connecting DateReceived, DateSold, and DateShipped columns from an inventory log to your date dimension.
• HR Analytics: Analyze employee data based on their HireDate, TerminationDate, or PromotionDate against a single master calendar.

Best Practices and Things to Watch Out For

Keep these points in mind when working with inactive relationships:

• Always use it inside CALCULATE: USERELATIONSHIP on its own doesn't do anything. It's a context modifier and needs to be used as a filter argument within a CALCULATE function.
• Check data granularity: Ensure that the relationship is set up correctly (e.g., one-to-many from the dimension table to the fact table) and that both columns in the relationship have the same data type.
• Keep modeling simple: The recommended approach is always to use a single dimension table (like a Calendar table) connected via multiple relationships to a fact table, rather than creating multiple copies of your dimension table. This keeps your model cleaner and more efficient.
• Performance: Overusing USERELATIONSHIP in extremely complex models with massive datasets can have performance implications, but for most common use cases, it is highly optimized and efficient.

Final Thoughts

Inactive relationships in Power BI aren't a limitation, they're a feature designed to create clean, unambiguous, and powerful data models. By combining them with the USERELATIONSHIP DAX function, you gain the ability to slice and dice your data from multiple perspectives without duplicating tables or creating a messy report structure.

But building robust data models, writing DAX, and troubleshooting formulas often feels like learning a new language. Sometimes, you just need a straightforward answer without the steep learning curve. At Graphed, we created a tool to eliminate that friction. Instead of manually setting up relationships and writing measures, you can simply connect your data sources - like Salesforce, Shopify, or Google Analytics - and ask questions in plain English, like "Show me a comparison of revenue by order date versus ship date for last quarter." Our AI analyst builds the dashboard for you in seconds, saving you time usually spent wrangling data, so you can get right to the insights.