USERELATIONSHIP in DAX: Fix an Inactive Date Relationship

USERELATIONSHIP tells CALCULATE to use an existing model relationship for one calculation, even if that relationship is inactive. The classic example is a Sales table related to one Date table by both Order Date and Ship Date: keep one path active by default, then activate the other in the measure that needs it.

It does not create a relationship, return a value, or permanently change the model.

The role-playing Date problem

Assume:

Date[Date] → Sales[Order Date]   active
Date[Date] → Sales[Ship Date]    inactive

Only one relationship between the same two tables can normally be the unambiguous active path. With Order Date active, this measure reports by order date:

Total Sales =
SUM ( Sales[Sales Amount] )

To report the same sales by ship date:

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

Place 'Date'[Year] or 'Date'[Month] on the visual. For this measure, the Date filter uses the Ship Date relationship during evaluation.

Microsoft’s USERELATIONSHIP reference specifies that the function uses an existing relationship identified by its endpoint columns and enables it for the duration of the calculation.

Build the relationships before writing the measure

In Model view:

1. create the active relationship from Date[Date] to Sales[Order Date];
2. create the second relationship from Date[Date] to Sales[Ship Date];
3. leave the second one inactive; and
4. confirm the Date table contains unique dates.

The inactive relationship should appear as a dashed line.

If the relationship does not exist, USERELATIONSHIP returns an error. Its arguments must be columns that are endpoints of the same model relationship.

Do not use USERELATIONSHIP as a substitute for a missing star schema.

USERELATIONSHIP belongs inside a filter-taking function

USERELATIONSHIP modifies evaluation; it does not produce a scalar or table result by itself.

Use it in functions that accept filters, most commonly CALCULATE:

Orders by Delivery Date =
CALCULATE (
    [Order Count],
    USERELATIONSHIP (
        Orders[Delivery Date],
        'Date'[Date]
    )
)

This is invalid as a standalone measure:

Wrong =
USERELATIONSHIP ( Sales[Ship Date], 'Date'[Date] )

The function returns no report value.

Compare the two date roles side by side

Create:

Sales by Order Date =
[Total Sales]

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

Then build a table:

Date field	Measure
'Date'[Month]	[Sales by Order Date]
'Date'[Month]	[Sales by Ship Date]

Both measures use the same Date axis. The active relationship handles the first measure; USERELATIONSHIP changes the path for the second.

If both measures return the same values, confirm the underlying order and ship dates actually differ. Equal results can be valid data, not a failed relationship switch.

Why the result is blank

If the USERELATIONSHIP measure returns blank, check:

The inactive relationship exists

Open Model view and select the dashed relationship. Verify its exact endpoints match the formula.

The visual uses the Date dimension

Use 'Date'[Date], 'Date'[Month], or 'Date'[Year] on the visual—not Sales[Order Date]. A fact-date field can filter the fact table directly in a way that conflicts with the intended role.

The Date table covers the alternate dates

If Ship Date extends later than Order Date, the Date table must include those later dates.

The fact date has matching values

Unshipped orders may have blank Ship Date. A ship-date measure should exclude them until a valid matching date exists.

Data types and time portions match

A Date dimension often contains midnight dates. A fact column containing timestamps may need a clean date key before the relationship can match reliably.

For previous-year measures over the alternate role, also use the SAMEPERIODLASTYEAR blank checklist.

Combine USERELATIONSHIP with time intelligence

You can apply the alternate relationship and a date-table shift in one CALCULATE:

Shipped Sales Previous Year =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP (
        Sales[Ship Date],
        'Date'[Date]
    ),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Read it as:

1. use the Ship Date relationship for this calculation;
2. shift the current Date context to the previous year; and
3. evaluate [Total Sales].

Test the relationship switch alone before adding time intelligence. A smaller expression makes it clear which step fails.

Multiple inactive relationships

A calculation can specify more than one USERELATIONSHIP when it must activate multiple existing paths:

Metric by Alternate Paths =
CALCULATE (
    [Base Metric],
    USERELATIONSHIP ( Fact[Alternate Date], 'Date'[Date] ),
    USERELATIONSHIP ( Fact[Alternate Key], Dimension[Key] )
)

Each function identifies one relationship. This is an advanced design; verify that the resulting paths are intentional and unambiguous.

For nested CALCULATE expressions with conflicting USERELATIONSHIP calls, Microsoft documents that the innermost relationship choice prevails. Avoid deep nesting when a clearer base measure can express the same rule.

USERELATIONSHIP and row-level security

There are restrictions when row-level security is involved. Microsoft’s documentation states that USERELATIONSHIP cannot be used when row-level security is defined for the table in which the measure is included in the documented scenario.

Treat this as a model-security design constraint, not a syntax puzzle. Test the measure under every role and consult the current product documentation before changing relationship direction or moving secured logic.

When to use another model design

USERELATIONSHIP is strong when:

• one Date dimension plays several roles;
• one role is the default;
• alternate roles are needed in specific measures; and
• report authors can understand the measure names.

Consider separate role-playing Date tables when:

• users need Order Date and Ship Date slicers at the same time;
• both date roles must filter visuals independently;
• self-service report authors need simpler fields; or
• many measures require the alternate path.

For example, Order Date and Ship Date dimensions can each have an active relationship to Sales. This adds model objects but can reduce measure complexity and user confusion.

A practical test sequence

1. Put 'Date'[Date] on rows.
2. Add [Total Sales].
3. Add [Sales by Ship Date].
4. Add COUNTROWS ( Sales ) under the active relationship.
5. Create a ship-date row-count measure with USERELATIONSHIP.
6. Filter to one order whose order and ship dates differ.
7. Confirm each measure appears on the expected date.
8. Test totals and blank Ship Dates.

This proves the path before you build year-to-date, previous-year, or delivery-lag calculations on top of it.

If relationship behavior still feels opaque, revisit row context versus filter context and the CALCULATE practice set.

Frequently asked questions

Does USERELATIONSHIP make an inactive relationship active permanently?

No. It enables the specified relationship only while the enclosing calculation is evaluated.

Can USERELATIONSHIP create a relationship?

No. Both columns must already be endpoints of an existing model relationship.

Which column comes first?

Microsoft documents that if the many-side and one-side arguments are reversed, the function swaps them before use. Use a consistent fact-column, then dimension-column order for readability.

Can I activate Ship Date and still use the same Date slicer?

Yes. The measure can use the Date slicer through the Ship Date relationship while other measures use the active Order Date relationship. Make the measure names explicit so readers know which role each one uses.

Where can I practice inactive relationships?

Use a DAX Solver challenge and draw the active and inactive paths before writing the measure. Then test one fact row whose two dates differ.