What is an Active Relationship in Power BI?

Setting up your data model is the first big step to unlocking insights in Power BI, and a key part of that is defining relationships between your tables. But what happens when you need to link two tables in more than one way, like analyzing sales by both order date and ship date? That’s where active relationships come in. This article explains exactly what an active relationship is in Power BI, why it’s important, and how to work with its counterpart, the inactive relationship, to perform more flexible 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

A Quick Refresher on Power BI Relationships

Before diving into active versus inactive relationships, let’s quickly revisit what relationships do. In Power BI, relationships tie your tables together based on common columns, creating a cohesive data model. They tell Power BI how to filter one table based on a value selected in another. For instance, clicking on "2023" in a date table should filter your sales table to only show sales from that year.

Each relationship has two main properties:

• Cardinality: This describes how the tables are related. The most common type is one-to-many (e.g., one customer can have many orders), but you can also have one-to-one, many-to-one, and many-to-many.
• Cross-filter direction: This determines the filtering flow. “Single” means one table filters another (e.g., your Calendar table filters your Sales table). “Both” means the filters can go in either direction, though this should be used cautiously to avoid ambiguity.

Understanding these basics is essential, because active and inactive relationships build directly on this foundation.

What Exactly is an Active Relationship?

An active relationship is the primary, default path Power BI uses to propagate filters between two tables. Think of it as the main highway connecting two cities. When data needs to travel from one place to another, this is the route it will always take unless explicitly told otherwise.

The single most important rule to remember is this: You can only have one active relationship between any two tables at one time.

This rule prevents ambiguity. If Power BI had two "main highways" open at once, it wouldn't know which one to use when filtering data, leading to confusing or incorrect results. So, it forces one path to be the primary (active) one.

In the Power BI relationship view, an active relationship is represented by a solid line connecting the two tables.

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

A Real-World Example

Let's consider a common business scenario. You have a fact table called Sales and a dimension table called DimDate (your calendar).

The Sales table contains two date columns:

• OrderDate: When the customer placed the order.
• ShipDate: When the warehouse shipped the order.

Your DimDate table has a single column, Date, containing every day of a given year.

The most common analysis you perform is on sales by when the order was placed. So, you create a relationship between DimDate[Date] and Sales[OrderDate]. This becomes your active relationship. Now, when you drag DimDate[Month] and your [Total Sales] measure into a chart, Power BI automatically filters your sales data based on the OrderDate.

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

Then What is an Inactive Relationship?

An inactive relationship is a secondary, dormant link between two tables. It exists in your model, but Power BI does not use it for filtering by default. Sticking with our analogy, an inactive relationship is like a scenic backroad. The road exists, but your GPS (Power BI) won't route traffic down it unless you specifically tell it to.

The need for an inactive relationship arises from the "one active relationship" rule. In our example, we've already used our one active path to link DimDate[Date] with Sales[OrderDate]. But what if the sales department also needs to analyze sales by the ShipDate?

You can't create another active relationship. Instead, you create a second relationship between DimDate[Date] and Sales[ShipDate]. Because an active one already exists, Power BI automatically makes this second relationship inactive.

In the relationship view, an inactive relationship is shown as a dashed line.

An inactive relationship on its own does nothing. Your charts will continue to use the active relationship by default. To use this secondary path, you need to "turn it on" for specific calculations using a bit of DAX.

How to Activate an Inactive Relationship Using DAX

The magic for leveraging inactive relationships lives in one powerful DAX function: USERELATIONSHIP.

This function tells Power BI, "For this calculation only, ignore the default active relationship and use this specific inactive one instead." It doesn't permanently change anything in your model, it just temporarily opens up that scenic backroad for one specific trip.

Let’s walk through how to create a measure that calculates total sales based on the ship date.

Step-by-Step Guide with USERELATIONSHIP

First, let's assume you have a standard measure for total sales, which implicitly uses the active OrderDate relationship:

Total Sales = SUM(Sales[SalesAmount])

Now, let's build our new measure to count sales by ship date.

1. Create a New Measure: Right-click on your Sales table and select "New measure."

2. Write the DAX Formula: Enter the following formula:

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

3. Understand the Formula:

• CALCULATE is the ultimate DAX function. It modifies the existing filter context of a calculation. Here, it allows us to alter how a measure is calculated.
• [Total Sales] is the expression we want to evaluate (our baseline sum of sales).
• USERELATIONSHIP(DimDate[Date], Sales[ShipDate]) is the filter modifier. This is the instruction telling CALCULATE to activate the inactive path between DimDate and the ShipDate column for this calculation only.

That's it! You now have two distinct measures. When you use Total Sales with a filter from your DimDate table, you’re analyzing by order date. When you use Sales by Ship Date with the same date filter, you’re analyzing by ship date. This gives you incredible analytical flexibility from a single 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

Common Use Cases and Best Practices

Once you understand the active/inactive concept, you'll see opportunities for it everywhere. Here are a few common scenarios and tips.

Use Cases:

• Multiple Dates: The most classic example. Analyzing sales by order date, ship date, or due date. Or tracking website visits by the session date versus a conversion date.
• Role-Playing Dimensions: Imagine a table for flight data with columns for OriginAirport and DestinationAirport, both pointing to a single Airports dimension table. One relationship can be active (e.g., origin), and measures can use USERELATIONSHIP to analyze by destination.
• Budget vs. Actuals: Sometimes your budget data is at a monthly level while your actuals are at a daily level. You might have relationships from your Date dimension to both your Budget table (Date -> BudgetMonth) and your Sales table (Date -> OrderDate), using different measures to compare performance.

Best Practices:

• Use a Dedicated Date Table: Always build and use a proper Date dimension (or Calendar table) in your models. Relying on Power BI's automatic date hierarchies can make managing relationships like this much more difficult.
• Set the Most Common Path as Active: Make your life easier by setting the relationship used most often by analysts as the active one. This reduces the number of custom DAX measures you need to build.
• Name Measures Clearly: Be explicit in your naming conventions. Measures like "Sales by Order Date" and "Sales by Ship Date" are far more intuitive for report users than "Sales" and "Sales 2."
• Keep Cross-Filtering Simple: In models with multiple relationship paths, stick to a Single cross-filter direction unless you have a proven and specific need for Both. This keeps the filtering logic clear and predictable.

Final Thoughts

In a nutshell, active relationships are Power BI's default-lane for connecting tables, while inactive relationships, brought to life with the DAX function USERELATIONSHIP, give you the flexibility to explore different analytical paths. Mastering this concept is a big step up from building basic reports and moves you toward creating robust, sophisticated data models that answer a wider range of business questions.

While powerful, setting up complex data models in Power BI and writing intricate DAX formulas can be a significant time investment, often involving a steep learning curve. We created Graphed to remove this friction. Instead of worrying about managing active relationships or perfecting your USERELATIONSHIP syntax, you can simply connect your data sources and ask questions in plain English, like "Show me a comparison of revenue by order date versus ship date for last quarter." We handle the underlying data complexities so you can go from data to insight in seconds, not hours.