Handle multiple dates in a fact table with a clear plan
Handle multiple dates in a fact table with a clear plan. Power BI only allows one active relationship. Your model often needs more. Order Date and Ship Date are common examples. Your visuals need flexibility.
USERELATIONSHIP gives you control.
Here is how you approach it:
• Keep one relationship active. Use Order Date as the default.
• Keep the other relationship inactive. Use Ship Date when needed.
• Create a base measure for sales.
• Create a second measure that activates the inactive relationship with
USERELATIONSHIP.
• Use this second measure when your report needs relationship-driven analysis.
Example:
Sales = SUM(FactSales[SalesAmount])
Sales Ship Date = CALCULATE( [Sales], USERELATIONSHIP(FactSales[ShipDate], DimDate[Date]) )
This measure switches the date context to Ship Date. Your visuals respond to the date that matters for the story you want to present.
Use this pattern when your fact table holds multiple date fields. Order, ship, delivery, invoice. USERELATIONSHIP helps you control which date drives your insights.
Your reports stay clean.
Your logic stays predictable.
Your audience gets the right numbers every time