USERELATIONSHIP DAX Function: Use Inactive Relationships in Power BI

USERELATIONSHIP in DAX: inactive relationships explained

Many tables have more than one date: an order has an Order Date, a Ship Date and a Delivery Date. Power BI allows only one active relationship between two tables, so when you connect all three dates to your Date table, two of the relationships become dotted (inactive) lines. To use them, you need the DAX function USERELATIONSHIP.

This guide explains active and inactive relationships, shows how to write measures with USERELATIONSHIP, and covers the alternative (role-playing date tables) and the common errors.

Quick answer: Create the extra relationship (for example Sales[ShipDate] → 'Date'[Date]); Power BI makes it inactive. Then write Sales by Ship Date = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) ). The measure uses the ship-date relationship instead of the active order-date relationship, only inside that calculation.

Active vs inactive relationships

  • Active relationship (solid line): used automatically by every visual and measure.
  • Inactive relationship (dotted line): exists in the model but does nothing until a measure switches it on with USERELATIONSHIP.

Power BI allows only one active path between two tables to avoid ambiguity: if both Order Date and Ship Date were active, a filter on October would not know which date to use.

Example model

Sales table: OrderID, OrderDate, ShipDate, Amount. Date table: one row per day, marked as a date table (see how to create a date table).

  1. In Model view, drag Sales[OrderDate] to 'Date'[Date]. This becomes the active relationship.
  2. Drag Sales[ShipDate] to 'Date'[Date]. Power BI creates it as inactive (dotted line).

To check or change which one is active: double-click the relationship line and look at Make this relationship active, or use Manage relationships.

USERELATIONSHIP syntax

USERELATIONSHIP ( <column1>, <column2> )
  • The two columns are the two ends of an existing relationship (the order does not matter).
  • It works only as a filter argument inside CALCULATE (or functions that take filter arguments, such as CALCULATETABLE and TOTALYTD).
  • While the measure runs, the inactive relationship becomes active and the normal one is ignored.

Example 1: Sales by order date vs ship date

Total Sales = SUM ( Sales[Amount] )

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

Put both measures in a table by month. Total Sales shows what was ordered each month; Sales by Ship Date shows what was shipped. The difference is your shipping backlog.

Example 2: Count orders shipped

Orders Shipped =
CALCULATE (
    COUNTROWS ( Sales ),
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)

Example 3: Time intelligence on the inactive date

Shipped Sales YTD =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ),
    DATESYTD ( 'Date'[Date] )
)

Combine USERELATIONSHIP with any time-intelligence filter in the same CALCULATE.

Example 4: HR headcount (hire and exit dates)

An Employees table has HireDate and ExitDate, both related to the Date table as inactive relationships. Then:

Hires =
CALCULATE ( COUNTROWS ( Employees ), USERELATIONSHIP ( Employees[HireDate], 'Date'[Date] ) )

Leavers =
CALCULATE ( COUNTROWS ( Employees ), USERELATIONSHIP ( Employees[ExitDate], 'Date'[Date] ) )

See the full HR model in Power BI HR analytics dashboard.

Alternative: role-playing date tables

Instead of one Date table with inactive relationships, you can create separate date tables: Order Date and Ship Date, each with an active relationship.

USERELATIONSHIPRole-playing date tables
Date tablesOneOne per date role
Extra measuresYes, one per date roleNo, existing measures work
SlicersOne date slicer for everythingSeparate slicers for order date and ship date
Best forA few measures on a second dateUsers who want to filter by both dates freely

A quick way to create a second date table: Modeling → New table → Ship Date = 'Date', then relate it to Sales[ShipDate] and mark it as a date table.

Related function: CROSSFILTER

USERELATIONSHIP chooses which relationship to use. CROSSFILTER changes the filter direction of a relationship inside a measure (one-way, both, or none). For example, CALCULATE ( DISTINCTCOUNT ( Sales[CustomerID] ), CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH ) ). Use it sparingly; bi-directional filters can slow models and cause ambiguity.

Common errors and fixes

"USERELATIONSHIP function can only use the two columns references participating in relationship"

The relationship does not exist. Create it in Model view first (it can stay inactive).

The measure returns the same values as Total Sales

Check that you referenced the right columns, and that the visual uses the Date table (not Sales[OrderDate]) on its axis.

It works in a table but not with my slicer

The slicer must use a column from the Date table. A slicer on Sales[OrderDate] filters Sales directly and bypasses the relationships.

Row-level security limitation

Microsoft's documentation notes that USERELATIONSHIP cannot be used when row-level security is defined for the table in which the measure is included. Test carefully if you use RLS.

Frequently asked questions

Can I have two active relationships between the same tables?

No. Only one can be active. Use USERELATIONSHIP or a second date table for the others.

Can I use USERELATIONSHIP outside CALCULATE?

No. It is a filter modifier and must be an argument of CALCULATE or a similar function.

Does USERELATIONSHIP slow down reports?

No more than any other CALCULATE filter. It is the standard, efficient way to work with multiple dates.

Practice task

Add OrderDate and ShipDate relationships from Sales to Date. Create Total Sales and Sales by Ship Date, show both by month in a line chart, and add a measure for the difference (the backlog).

Related: CALCULATE in DAX: Complete Guide and Power BI data modeling best practices.

Tags

Post a Comment

0 Comments
* Please Don't Spam Here. All the Comments are Reviewed by Admin.
Learn Excel, Power BI, and Finance in Simple Hindi and English Follow step-by-step tutorials and practical examples designed for students, job seekers, and working professionals. We cover Power BI dashboards, DAX formulas, Excel tricks, financial literacy, and AI tools.