Level 7 · An inactive relationship for signup dates

One calendar can describe several dates: when an order was placed, when it shipped, when the customer signed up. A Date table used like this is called a role-playing dimension, and only one of its roles can be active between the same tables.

What you are working in

This is a practice diagram built for this site; Power BI Desktop runs only on Windows. The existing relationships are drawn and locked. Add the new one with the form, including the active setting; the diagram refuses a second active path the way Power BI does.

In Power BI Desktop: Modeling → Manage relationships → New, choose customers and signup_date, then Date and Date, Many to one (*:1), Single, and clear Make this relationship active. In Model view an inactive relationship is drawn as a dashed line.

Why there can only be one active path

Trace the filters from 'Date' today:

'Date' → orders            (order_date, active)
orders → customers?        no: filters flow from customers to orders, not back

But add customers[signup_date] > 'Date'[Date] as active and 'Date' relates to both orders and customers, while customers also relates to orders. Now a filter for March 2025 could reach orders directly (orders placed in March) or through customers (orders by customers who signed up in March). Two answers to one question: Power BI calls this an ambiguous path and won’t create it as active.

Inactive, not useless

An inactive relationship sits in the model doing nothing until a measure asks for it:

New Signups =
CALCULATE(
    COUNTROWS(customers),
    USERELATIONSHIP(customers[signup_date], 'Date'[Date])
)

Inside that CALCULATE the signup relationship is active and the order-date path is set aside, so 'Date'[Month] on the rows gives signups per month. Every other measure keeps using the order date. You will write measures like this in the advanced DAX chapter.

Which one should be active?

The one most measures need. Sales, units and orders all follow the order date, so that one stays active; the signup date serves one or two measures.

Common mistakes

• Making it active anyway. Power BI either refuses or turns another relationship off, and sales by month change without warning.
• A second Date table “to avoid the problem”. It works, but doubles the calendar and the slicers. One calendar plus USERELATIONSHIP is the usual answer.
• Forgetting it exists. An inactive relationship never filters anything by itself. If a signup chart shows order months, the measure is missing USERELATIONSHIP.