In Power BI, how do you model both order date and ship date against one Date table?

## What role-playing means here A dimension is *role-playing* when the same entity describes a fact in more than one capacity. A calendar date is the archetype: an order has an order date, a ship date, a due date and possibly a return date, all of which are dates from the same calendar. `Customer` does it too — a shipment has a sender and a recipient, both customers. Relational sources handle this with several foreign keys pointing at one dimension table. Power BI cannot, at least not directly, because of a rule in its relationship engine. ## The one-active-path rule Between any pair of tables, only **one** relationship path may be active at a time. Create the second relationship from `Date[Date]` to `Sales[ShipDate]` and Power BI creates it **inactive**: a dashed line in the model diagram, present but not participating in filter propagation. The rule exists so that a filter always has exactly one route to a table and therefore exactly one possible answer. The practical effect is that with an active relationship on `OrderDate`, *every* measure and *every* visual is filtered by order date. A slicer set to March shows sales **ordered** in March, whatever the report label says. That mislabelling — someone assuming the date slicer means ship date — is a routine source of "why is this number wrong". ## Approach one: one date table, role-specific measures Keep the single `Date` table with the active relationship on the most-used role, and write measures that borrow the inactive relationship: ```dax Sales Ordered = SUM ( Sales[Amount] ) Sales Shipped = CALCULATE ( SUM ( Sales[Amount] ), USERELATIONSHIP ( 'Date'[Date], Sales[ShipDate] ) ) ``` `USERELATIONSHIP` activates the named relationship for the duration of that `CALCULATE` only. Now a single date slicer drives both measures, and a table showing them side by side answers "what did we take in versus what did we get out of the door" in one visual — genuinely hard to build with two separate date tables. The costs: the behaviour is invisible on the model diagram, so a colleague sees a dashed line and no explanation; and every metric that needs the second role requires its own measure, which multiplies if you have four date roles and thirty measures. ## Approach two: duplicate the date table Load the calendar a second time under a clear name — `Order Date` and `Ship Date`, each with its own columns prefixed accordingly — and give each its own active relationship. Now the model is entirely self-describing: users drag `Ship Date[Month]` onto an axis and every measure follows without any special variant. This is the standard warehouse view of role-playing dimensions (typically implemented as views over one physical dimension) carried into Power BI. The costs: a duplicated table (small for a calendar, larger for `Customer`); two slicers that users can set inconsistently; and the awkwardness of comparing roles side by side, since each measure now follows whichever calendar it is grouped by. ## Choosing between them It is a genuine trade-off, and interviewers ask precisely because it has no single answer. Use duplicated tables when the report audience is self-service and needs to slice by each role independently — discoverability wins. Use `USERELATIONSHIP` when the roles must appear together in one visual under one slicer, when the measure count is small, or when the dimension is too large to duplicate comfortably. Many real models do both: a duplicated table for the primary alternative role, and `USERELATIONSHIP` measures for rarer ones. ## Related mechanics worth knowing Whichever route you take, the calendar table should be a real, contiguous date table and should be marked as a date table so time-intelligence calculations behave predictably. Power BI's automatic date/time feature also generates hidden per-column date tables in import models, which inflates model size and produces date hierarchies that do not match your calendar; most modellers turn it off and use an explicit calendar. And note the terminology trap: an *inactive relationship* is not a *deleted* one, and it is not the same as a relationship whose cross-filter direction is `None` for a measure — the first is activated with `USERELATIONSHIP`, the second is manipulated with `CROSSFILTER`. ## What interviewers listen for The answer must start from the one-active-path rule, then present both remedies with their costs. A weak answer proposes deleting one of the date columns, or claims Power BI simply cannot model two dates against one calendar.