DAX inactive relationships and order versus delivery dates

An order can be placed on one date and delivered on another. A report must identify which date role determines its period. An inactive relationship can support an alternate date measure without changing the default order-date relationship for every visual.

Name the alternate measure clearly and test dates where the populations differ. Monthly totals can accidentally agree when every delivery happens in the same month as its order.

Configure the two date paths

In the retail lab, relate DimDate[Date] actively to FactSales[OrderDate]. Add a second relationship from DimDate[Date] to FactSales[DeliveryDate] and leave it inactive.

Order O1's two lines were ordered January 3 and delivered January 5. O2 was ordered January 5 and delivered January 8. Pending line S5 has no DeliveryDate.

Set all relevant columns to compatible Date types. A timestamp with a time component requires a deliberate reporting-date derivation before matching a daily date table.

Define the alternate measure

Use the Paid Net Paise baseline, then create:

Paid Net by Delivery Date Paise =
CALCULATE(
    [Paid Net Paise],
    USERELATIONSHIP(DimDate[Date], FactSales[DeliveryDate])
)

USERELATIONSHIP identifies an existing relationship to use during the calculation. In this simple two-date-role setup it changes the relevant date path for this measure, rather than globally changing the model's default relationship. Microsoft documents the function and its restrictions in the USERELATIONSHIP reference.

Test model-specific security and relationship constraints before applying the pattern in a more complex semantic model.

Compare daily results

On January 3, order-date Paid value is 24,000 paise from S1 and S2. Delivery-date Paid value has no qualifying delivered lines on that date.

On January 5, order-date Paid value is 10,000 from S3, while delivery-date Paid value is 24,000 from S1 and S2. On January 8, delivery-date Paid value is 10,000 from S3.

These dates reveal the semantic difference. Both January totals happen to be 47,500 in the clean fixture because its Paid January orders are also delivered in January. A monthly-only test would not prove that the alternate relationship is working.

Keep status and date roles separate

The measure remains Paid-only because the base measure defines that eligibility. Activating DeliveryDate does not mean every delivered order is paid, and selecting Paid does not prove delivery has occurred in a different dataset.

If the business asks for delivered value regardless of payment status, create a separate definition with the appropriate delivery and status rules. Do not reuse a Paid measure under a Delivered title merely because the current fixture has overlapping populations.

Blank delivery dates need an explicit interpretation. They may mean not delivered, unavailable data or not applicable, depending on the source contract.

Avoid conflicting date controls

Use DimDate fields for the intended shared date selector in this exercise. Additional filters directly on FactSales[OrderDate] can still constrain the population and create intersections the reader may not expect.

If the report needs simultaneous independent order-date and delivery-date slicers, separate role-playing date dimensions may provide a clearer design. Define which measures respond to each and test the combined selection.

A single alternate relationship does not automatically solve every multi-date reporting requirement.

Validate boundary-crossing cases

In a source copy, move one January order's delivery into February. Confirm that order-month totals stay with January while delivery-month totals move the corresponding value to February.

Reconcile the full eligible total across the complete delivery observation window. If some Paid orders remain undelivered, a delivered-only total can legitimately be lower than the order-date total and should be labelled accordingly.

The lab verifies source rows and amounts in SQLite. It does not evaluate USERELATIONSHIP or deliver a tested report; record those results in the actual Power BI model.

Exercise: add a Paid order with a blank delivery date. Define whether the delivery report excludes it, shows an undelivered queue or blocks a completeness claim. Explain why its absence from a delivery-period chart is not necessarily a missing order.

NeuraPath's Data Analytics with Generative AI course connects DAX relationships with event semantics. A reliable date-role measure states which business event places each record in the period.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Create a disconnected scenario table in Power BI.
• Continue with Calculate customer repeat rate without double-counting visits.