How to Have Two Active Relationships in Power BI

Working in Power BI, you've likely hit that infamous roadblock: you can only have one active relationship between two tables. This can feel incredibly limiting, especially when your data has multiple important dates or connections. A classic example is a sales table with both an 'Order Date' and a 'Ship Date' - how do you analyze your revenue by both contexts without pulling your hair out?

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

There's good news. While Power BI's rule is firm, there are two standard and powerful ways to work around it. This guide will walk you through both methods, helping you choose the right one for your specific reporting needs.

Why Does Power BI Limit You to One Active Relationship?

Before diving into the solutions, it's helpful to understand why this limitation exists. It's all about preventing ambiguity. Imagine you have a Sales table and a Calendar table, with two relationships active at the same time: one on OrderDate and another on ShipDate.

If you build a chart showing 'Sales by Year,' how would Power BI know which date to use? Should it calculate sales based on when the order was placed or when it was shipped? Without a clear, single path, your calculations would be unpredictable and likely incorrect. The engine would be confused, and you’d get unreliable analysis.

To avoid this, Power BI enforces a single active path for calculations. Any additional relationships you create between the same two tables are set to 'inactive,' represented by a dotted line in the model view. Our job is to tell Power BI when and how to use those inactive paths.

Method 1: Using the USERELATIONSHIP Function in DAX

The first and most direct way to use an inactive relationship is with a DAX function called USERELATIONSHIP. This function doesn't change the model itself, instead, it allows you to temporarily activate an inactive relationship just for a single calculation.

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

When to Use This Method

The USERELATIONSHIP approach is ideal when you only need a few specific measures based on the inactive relationship. If users of the report won't be building their own visuals and you just need to display a 'Sales by Ship Date' metric alongside your 'Sales by Order Date' metric, this is the most efficient choice.

Benefits:

• It keeps your data model clean and lean, with no extra tables.
• It's computationally efficient.

Step-by-Step: Creating a Measure with USERELATIONSHIP

Let's use our common sales scenario. Your model has a Sales table and a Calendar table. The active relationship (solid line) is between Calendar[Date] and Sales[OrderDate]. The inactive relationship (dotted line) is between Calendar[Date] and Sales[ShipDate].

1. Create Your Base Measure

First, create a standard measure for total revenue. Because the active relationship is on OrderDate, this measure will always calculate sales based on when the order was placed.

Total Revenue = SUM(Sales[Revenue])

2. Create the New Measure Using USERELATIONSHIP

Next, you'll create a new measure for sales calculated by the ship date. This is where the magic happens. We'll use the CALCULATE function to modify the context of our existing [Total Revenue] measure.

The USERELATIONSHIP function takes two arguments: the column from the 'one' side of the relationship (Calendar[Date]) and the column from the 'many' side (Sales[ShipDate]).

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

This formula tells Power BI: "For this calculation only, ignore the active relationship and instead, use the relationship between the Calendar table's Date column and the Sales table's ShipDate column."

3. Visualize the Results

Now you can build a table or chart. Drag a date field (like Month or Year) from your Calendar table. Then add both measures: [Total Revenue] and [Revenue by Ship Date]. You will now see both figures side-by-side, sliced by the same date period, but calculated based on different date logic.

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

Pros and Cons of USERELATIONSHIP

Pros:

• Clean Model: Avoids adding clutter to your model view.
• Specific: Perfect for a small number of targeted calculations.

Cons:

• Tedious Repetition: If you need to analyze profit, units sold, and shipping costs by ship date, you have to create a separate USERELATIONSHIP measure for each one. This can quickly become cumbersome.
• Not User-Friendly for Self-Service: If your team members want to build their own reports, they can't simply drag the sales amount and a ship date field onto the canvas. They would need to know which special DAX measure to use.

Method 2: Duplicating Tables in the Data Model

If the DAX approach sounds too tedious, the second option is to restructure your data model slightly. This method involves creating a copy of your dimension table (in our case, the Calendar table) so that each copy can play a specific "role." This is a common modeling technique often called a 'role-playing dimension.'

When to Use This Method

Duplicating a table is the best approach when you need to enable intuitive, self-service analysis for your users. If you want a non-technical user to be able to drag-and-drop 'Revenue' and slice it by OrderDate or ShipDate without needing to know any DAX, this is the way to go.

Benefits:

• Creates a very intuitive user experience for report builders.
• No need to write a new DAX measure for every calculation.

Step-by-Step: Duplicating a Table

Continuing our example, here's how to set up role-playing date dimensions.

1. Open Power Query and Duplicate Your Table

Go to 'Transform data' to open the Power Query Editor. Find your Calendar table in the Queries pane. Right-click on it and select 'Duplicate.' A new query called "Calendar (2)" will appear.

Rename the tables for clarity. For instance:

• Original table: Order Calendar
• Duplicate table: Ship Calendar

2. Close and Apply

Once you've renamed your tables, click 'Close & Apply.' Power BI will now load two identical calendar tables into your data model.

3. Adjust the Relationships in the Model View

Go to the Model view in Power BI. You'll now see your Sales table and your two new calendar tables.

• Delete any existing relationships between the original calendar and the sales tables.
• Create a new active relationship by dragging Order Calendar[Date] to Sales[OrderDate].
• Create a second active relationship by dragging Ship Calendar[Date] to Sales[ShipDate].

You now have two separate, fully active relationships. Power BI is no longer confused because the paths originate from two different tables.

4. Build Your Visuals Intuitively

Here's where this method shines. A user can now:

• Create a chart for sales by order date by dragging 'Total Revenue' from the Sales table and 'Month' from the Order Calendar table.
• Create another chart for sales by ship date by dragging 'Total Revenue' from the Sales table and 'Month' from the Ship Calendar table.

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

Pros and Cons of Duplicating Tables

Pros:

• User-Friendly: It's extremely intuitive for anyone building reports. Drag, drop, and you're done.
• Scalable: Slicing any measure (revenue, profit, units, count of orders) by either date context works automatically without writing new DAX.

Cons:

• Model Complexity: It adds more tables to your model, which can look cluttered. Good naming conventions are essential.
• Slightly Larger File Size: The model will contain more data, though for a dimension like a Calendar table, the impact is minimal.

So, Which Method is Right for You?

Deciding between these two powerful techniques comes down to your project's goals and who will be using the report.

Use the USERELATIONSHIP (DAX) method when:

• You’re creating a fixed report where users primarily consume, not build.
• You only need a couple of alternative calculations.
• You want to keep your data model as simple and small as possible.

Use the Duplicate Table (Data Modeling) method when:

• You are building a model for self-service analytics where users will create their own visuals.
• You need to analyze many different measures based on the different relationships.
• You prefer an intuitive, drag-and-drop experience over writing DAX.

Final Thoughts

Navigating Power BI's single active relationship rule is a mandatory skill for any serious analyst. By mastering both the USERELATIONSHIP function in DAX and the role-playing dimension technique, you empower yourself to build more flexible, insightful, and user-friendly reports that accurately reflect the complexities of your business data.

Of course, spending time managing data models, duplicating tables, and writing DAX is the kind of busy work that traditional BI tools require just to answer basic business questions. With Graphed, we've automated away that complexity. You connect your data sources directly, then simply ask "show me revenue by ship date vs order date" in plain English, and the platform builds the analysis for you - no data modeling required. It lets you get straight to the insights instead of getting stuck on the setup.