What is USERELATIONSHIP in Power BI?

If you've ever built a data model in Power BI, you know that relationships are the foundation of everything. You've probably also run into a frustrating limitation: you can only have one active relationship between two tables at a time. This article will show you how the USERELATIONSHIP function in DAX is the perfect solution for this exact problem, allowing you to use those "inactive" relationships to unlock deeper insights in your reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding Active vs. Inactive Relationships in Power BI

Before we get into the DAX function itself, let's quickly review how Power BI relationships work. When you connect two tables in your data model, like a Sales table and a Calendar table, Power BI creates a relationship. By default, this relationship is "active," which means it's the primary path that Power BI uses to filter and calculate data.

For example, if you connect your Sales[OrderDate] column to your Calendar[Date] column, any visual you create with dates from the Calendar table (like a line chart showing sales by month) will automatically filter the Sales table based on the order date.

But what if your Sales table has another date column, like ShipDate? You might want to analyze your sales by both the date an order was placed and the date it was shipped. You can create a second relationship connecting Sales[ShipDate] to Calendar[Date], but you'll notice in the Model View that this new relationship is represented by a dotted line. It's an inactive relationship.

Power BI defaults to this because having two active paths between the same two tables would create ambiguity. If you asked for "sales in January," Power BI wouldn't know whether to filter by the order date or the ship date. So, it keeps one active (the solid line) and makes any others inactive (the dotted lines).

This is where USERELATIONSHIP comes in. It's the key that lets you temporarily "turn on" those inactive relationships for specific calculations.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is the USERELATIONSHIP Function?

USERELATIONSHIP is a DAX function that activates a specific, existing inactive relationship between two columns for the duration of a calculation. It doesn't permanently change the relationship status, it just overrides the default active relationship for a single measure.

It's most commonly used inside a CALCULATE function, where it acts as a filter modifier. The syntax is straightforward:

USERELATIONSHIP(<columnName1>, <columnName2>)

• columnName1: The 'many' side of the relationship, which is typically your fact table column (e.g., Sales[ShipDate]).
• columnName2: The 'one' side of the relationship, which is typically your dimension table column (e.g., Calendar[Date]).

The order of the columns matters. Always specify the column from the "many" side of the relationship first, followed by the column from the "one" side.

Why (and When) You Need It: A Practical Example

The most common scenario where USERELATIONSHIP is essential involves date calculations. Nearly every business needs to analyze performance based on multiple "key dates."

Let's consider an e-commerce company tracking sales data. Their main 'Sales' table might have columns like:

• OrderID
• OrderDate
• ShipDate
• DeliveryDate
• SalesAmount

The business wants to build a dashboard that can answer several questions:

1. How much revenue was booked in Q1? (Based on OrderDate)
2. How much revenue was shipped in Q1? (Based on ShipDate)
3. How much revenue was delivered in Q1? (Based on DeliveryDate)

Without USERELATIONSHIP, this is tricky. The default active relationship might be set to OrderDate. All your standard sales measures would calculate "booked" revenue. To get shipped or delivered revenue for the same period, you'd need a way to tell Power BI to temporarily use those other date relationships instead.

USERELATIONSHIP allows you to create separate measures for each scenario, all using the same Calendar table as a slicer. This keeps your reports clean, consistent, and easy for end-users to understand. You can have a single "Quarter" filter on your report, and all three measures – Sales by Order Date, Sales by Ship Date, and Sales by Delivery Date – will update correctly.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Use USERELATIONSHIP in Power BI: A Step-by-Step Guide

Let's walk through how to implement this using our e-commerce example. Imagine we have two simple tables:

1. Sales Data (Sales_Data)

2. Calendar Table (Calendar_Table)

A standard calendar table with a continuous list of dates in a column named Date, along with columns for Year, Month, week, etc.

Step 1: Set Up the Relationships in the Model View

First, navigate to the Model View in Power BI. Create two relationships between your Sales_Data and Calendar_Table tables:

• Connect Sales_Data[OrderDate] to Calendar_Table[Date]. Power BI will automatically make this relationship active—a solid line.
• Connect Sales_Data[ShipDate] to Calendar_Table[Date]. Power BI will automatically make this relationship inactive to avoid ambiguity—represented by a dotted line.

Your model view should now show one solid and one dotted relationship line connecting the tables.

Step 2: Create a Base Measure

It's always good practice to create a base measure for your core metrics. Let's create a simple measure for total sales:

Total Sales = SUM(Sales_Data[SalesAmount])

If you drag this measure into a table or chart alongside month names from your Calendar_Table, Power BI would calculate sales based on OrderDate because it's the active relationship.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Create the USERELATIONSHIP Measure

Now, for the main event. We need a new measure that calculates sales based on ShipDate. We'll use our base [Total Sales] measure inside a CALCULATE function and tell it to use the inactive relationship.

Create another measure with this formula:

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

Breakdown:

• CALCULATE([Total Sales], ...): Tells DAX to start with the Total Sales calculation but modify its context before returning the result.
• USERELATIONSHIP(...): Activates the relationship between Sales_Data[ShipDate] and Calendar_Table[Date] only for this calculation, overriding the default active relationship.

Step 4: Visualize the Results

Now, create a matrix visual on your report canvas:

• Rows: Drag MonthName from Calendar_Table.
• Values: Drag both [Total Sales] and [Sales by Ship Date] measures.

The result will look something like this:

Analysis:

• For January: Orders totaling $1,700 were placed (OrderID 101 + 102), but only order 101 for $500 was shipped.
• For February: Orders totaling $1,150 were placed (OrderID 103 + 104), while $1,500 was shipped out (OrderID 102 + 103).

You’ve successfully built a visual that analyzes the same metric from two different time perspectives, all driven by a single date slicer!

Common Mistakes and Best Practices

While USERELATIONSHIP is powerful, keep these tips in mind:

• Relationship Must Exist: You can only activate a pre-existing inactive relationship. Define it in the Model View first.
• Only Works Inside CALCULATE: Its purpose is to modify filter context, so it should be used inside CALCULATE or similar functions.
• Always Use a Calendar Table: Analyses relying on date relationships should have a dedicated Calendar (or Date) table. Avoid making relationships directly between fact table date columns, as this can break Power BI’s time intelligence features.
• USERELATIONSHIP vs. Role-Playing Dimensions: Instead of creating multiple calendar tables for different date roles (order date, ship date, delivery date), USERELATIONSHIP offers a more efficient, scalable solution without bloating your data model.

Final Thoughts

The USERELATIONSHIP function is an essential tool for any Power BI user who needs to analyze data against multiple date fields or other dimensions with multiple roles. By enabling you to activate inactive relationships on-demand, it allows for more sophisticated, flexible, and insightful reporting without cluttering your data model.

We know managing complex data models can sometimes feel overwhelming. That’s why we built Graphed — our platform designed to reduce hours of manual reporting and help you focus on insights. Instead of writing complex formulas for different date relationships, connect your data sources and ask, "Compare my sales by ship date versus order date for last quarter," to get an interactive dashboard in seconds.