How to Use USERELATIONSHIP in Power BI

When you're building a data model in Power BI, it's common to deal with tables that have multiple meaningful dates. A single sales record, for example, might have an order date, a ship date, and a delivery date. You naturally want to analyze your sales across all of them, but Power BI only allows one active relationship between two tables at a time. This is where the powerful USERELATIONSHIP DAX function comes to the rescue. This article will walk you through exactly what USERELATIONSHIP does, why it's necessary, and how to use it with a practical, step-by-step example.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why You Can't Have Two Active Relationships

First, it's important to understand why Power BI has this "one active relationship" rule. It's all about avoiding ambiguity. A data model works by propagating filters from one table to another through relationships. Imagine you have a 'Sales' table and a 'Calendar' table.

• Your Sales table has a 'Total Sales' column, an 'OrderDate' column, and a 'ShipDate' column.
• Your Calendar table has a 'Date' column you want to use for filtering.

If you connect Calendar[Date] to Sales[OrderDate] AND Calendar[Date] to Sales[ShipDate] with two active relationships, Power BI gets confused. If a user selects "January 2024" from a slicer connected to your Calendar table, should Power BI filter the Sales table to show sales that were ordered in January or sales that were shipped in January? The model has two possible paths to take, and it has no way of knowing which one you intend.

To prevent this confusion, Power BI forces one relationship to be active (represented by a solid line in the Model View) and all other potential relationships between the same two tables to be inactive (represented by a dotted line). The active relationship is the default path used for all standard calculations. The USERELATIONSHIP function allows you to temporarily ignore that default and use one of the dotted-line, inactive paths for a specific measure.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is the USERELATIONSHIP Function?

The USERELATIONSHIP function is a DAX function that activates an existing, but inactive, relationship for the duration of a specific calculation. It doesn't return a value on its own, instead, it's used as a filter argument inside another function — most commonly, CALCULATE.

The syntax is straightforward:

USERELATIONSHIP(<columnName1>, <columnName2>)

Here, <columnName1> and <columnName2> are the two columns that define the endpoints of the inactive relationship you want to activate. The order you list them in doesn't matter. The key thing to remember is that this relationship must already exist in your data model as a dotted-line, inactive relationship.

Think of it as pointing at a dormant path in your model and saying, "Hey Power BI, for this one specific calculation I'm running right now, ignore the highway and take this scenic route instead."

Practical Example: Analyzing Sales by Order Date vs. Ship Date

Let's build this out to see it in action. The best way to learn is by doing, so follow these steps to see how you can analyze sales by both order date and ship date in the same report.

Step 1: Build Your Data Model with Multiple Dates

First, you need a basic data model. For this example, let’s assume you have two tables:

• A Sales table with columns like OrderID, Product, SalesAmount, OrderDate, and ShipDate.
• A Calendar table (a date dimension) with a Date column and other useful columns like Year, Month-Year, etc. A dedicated Calendar table is a best practice in any Power BI model.

Now, go to the Model View in Power BI and create the relationships:

1. Click and drag the Date column from your Calendar table onto the OrderDate column in your Sales table. Power BI will create an active relationship, represented by a solid line. This will be our default connection.
2. Next, click and drag the Date column from your Calendar table onto the ShipDate column in your Sales table. This time, Power BI will create an inactive relationship, shown as a dotted line, because an active path already exists.

Your model view should look something like this: one solid line connecting on the order date, and one dotted line for the ship date. This is the perfect setup for using USERELATIONSHIP.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Create a Basic Sales Measure

Before using USERELATIONSHIP, we need a base measure that calculates total sales. This measure will automatically use the active relationship.

In the Report View, create a new measure with this simple DAX formula:

Total Sales = SUM(Sales[SalesAmount])

If you create a table visual now, put Calendar[Month-Year] on the rows, and add this [Total Sales] measure, you would see your sales totals correctly broken down by the month the orders were placed. This is because it follows the solid-line, active relationship (OrderDate).

Step 3: Create a New Measure Using USERELATIONSHIP

Now for the main event. We want to create a second measure that calculates sales based on when the items were shipped. This requires us to temporarily activate that inactive, dotted-line relationship.

Create another new measure with the following DAX formula:

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

Let's break down what this formula does:

• CALCULATE(...): This is the most important function in DAX. It modifies the context in which a calculation is performed. We use it here to tell Power BI to change its normal behavior.
• [Total Sales]: This is the expression we want to evaluate. By reusing our base measure, we keep our code clean and maintainable. This is DAX best practice.
• USERELATIONSHIP(Sales[ShipDate], Calendar[Date]): This is the filter argument that does the magic. It instructs CALCULATE to deactivate the existing relationship to OrderDate for this calculation only and activate the one connecting Sales[ShipDate] to Calendar[Date].

Step 4: Putting It All Together in a Report

The "aha!" moment comes when you visualize these two measures together. In Power BI, create a new table or matrix visual.

1. Drag Calendar[Month-Year] (or any other field from your Calendar table) into the Rows field.
2. Drag your base measure, [Total Sales], into the Values field.
3. Drag your new measure, [Sales by Ship Date], into the Values field.

The result is a single table that shows your sales figures side by side, perfectly aligned by month. The first column shows sales based on when the order was placed, and the second shows sales based on when it was shipped. You can now easily compare if January’s orders shipped in January or February, providing much deeper business insights without needing any complex workarounds.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Common Scenarios for USERELATIONSHIP

The order/ship date scenario is the most common use case, but USERELATIONSHIP is powerful in other situations too. It's especially useful for "role-playing dimensions."

For example, imagine a 'Flights' table and a 'Cities' table. The Flights table would have both a DepartureCityID and an ArrivalCityID. You could have one active relationship between Cities[CityID] and Flights[DepartureCityID], and an inactive one for arrivals. Then, you could write a measure using USERELATIONSHIP to count flights arriving in a specific city, while your default measure counts departures.

Key Considerations and Best Practices

As you start using this function, keep a few key points in mind:

• Must Be Used Inside CALCULATE: USERELATIONSHIP doesn't do anything on its own. It serves as an instruction within a CALCULATE or CALCULATETABLE function to modify the filter context.
• The Relationship Must Pre-exist: You cannot use this function to create a new relationship out of thin air. You must define the pathway as an inactive relationship in the Model View first.
• Temporary and Specific: The change only applies for the specific calculation in which it's used. It doesn't permanently change your data model or affect any other visuals or measures in your report.
• Consider the Alternatives (and why this is often better): An alternative to USERELATIONSHIP is to duplicate your dimension table. For instance, you could have a 'Calendar Orders' table and a 'Calendar Shipments' table. However, this approach clutters your model with extra tables, consumes more memory, and can make your reports more difficult to manage. For most cases, USERELATIONSHIP is the cleaner and more efficient solution.

Final Thoughts

Managing multiple relationships between tables is a common yet essential task for any serious Power BI user. The USERELATIONSHIP function provides an elegant and efficient solution, allowing you to build rich, insightful reports that analyze metrics from different perspectives using a single, clean data model.

This level of DAX control is powerful but writing, testing, and managing measures is often where reporting workflows slow down. At Graphed, we created a way to handle this complexity for you. Instead of writing DAX, our platform lets you simply ask in plain English: "Compare my total sales by order date vs. ship date for last quarter." We instantly connect to your live data sources, generate the analysis, and build the right visualization for you, turning hours of tedious work into seconds of effortless conversation. It's about empowering you to get straight to an answer, not getting stuck on the technical setup.