Power BI Exercise - Using an Inactive Date Relationship with USERELATIONSHIP

Analyse the same retail transactions by sale date or ship date.

In this guided exercise, you will add a synthetic ShipDate column to the retail dataset, connect it to the existing Date table with an inactive relationship, and create measures that temporarily activate the shipping-date path.

Learning goal: understand role-playing dates, active and inactive relationships, and how USERELATIONSHIP changes the date path used by a calculation.

Active relationshipInactive relationshipUSERELATIONSHIPRole-playing date

Why use two date relationships?

A transaction can contain several dates. In this exercise:

• SaleDate represents when the sale was recorded.
• ShipDate represents when the item was shipped.

Both columns need calendar information from the same Date table. However, Power BI permits only one active filter path between these two tables at a time. SaleDate remains the default active relationship; ShipDate becomes inactive and is used only by selected measures.

Solid line = active default path. Dashed line = inactive path activated only by a measure.

Before you begin

Use the completed retail star-schema model. Confirm that:

• The Date table spans the complete 2026 calendar year.
• Date[Date] has an active one-to-many relationship to Sales[SaleDate].
• [Total Sales] returns 21,788 with no filters.

Step 1: Create the ShipDate calculated column

1. Open Data view and select the Sales table.
2. Select New column.
3. Enter the following formula.

ShipDate =
Sales[SaleDate]
    + MOD ( Sales[SaleID], 3 )
    + 1

The formula adds between one and three days to each SaleDate. It creates predictable synthetic shipping dates for this learning exercise.

Training data only: a production model should import the actual shipping date from the operational system. Do not generate shipping dates for real business reporting.

Step 2: Verify sample shipping dates

SaleID	SaleDate	Expected ShipDate
1001	3 Jan 2026	6 Jan 2026
1002	4 Jan 2026	5 Jan 2026
1008	17 Jan 2026	18 Jan 2026
1013	28 Jan 2026	31 Jan 2026
1015	31 Jan 2026	2 Feb 2026

Step 3: Create the inactive relationship

1. Open Model view.
2. Drag Date[Date] onto Sales[ShipDate].
3. Set cardinality to One to many (1:*).
4. Set cross-filter direction to Single.
5. Clear Make this relationship active.
6. Select OK.

The new ShipDate relationship should appear as a dashed line. The existing SaleDate relationship remains solid and active.

Do not make both relationships active. Power BI requires a deterministic default filter path between Date and Sales.

Step 4: Observe the default active relationship

Create a table containing Date[Year Month] and [Total Sales]. Because the SaleDate relationship is active, the result is grouped by the date of sale:

Year Month	Total Sales by SaleDate
2026-01	21,788

Step 5: Create Sales by Ship Date

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

For this measure only, USERELATIONSHIP activates the ShipDate relationship and overrides the competing active SaleDate relationship.

Step 6: Create Transactions Shipped

Transactions Shipped =
CALCULATE (
    [Transaction Count],
    USERELATIONSHIP (
        Sales[ShipDate],
        'Date'[Date]
    )
)

Step 7: Compare monthly results

Add Date[Year Month], [Total Sales], [Sales by Ship Date], and [Transactions Shipped] to a Table visual.

Year Month	Sales by SaleDate	Sales by ShipDate	Transactions shipped
2026-01	21,788	21,236	14
2026-02	Blank	552	1
Total	21,788	21,788	15

The Fast Charger was sold on 31 January but shipped on 2 February. The same transaction therefore contributes to January sales and February shipped sales.

Step 8: Validate in DAX Query View

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Date'[Year Month],
        "Sales by SaleDate", [Total Sales],
        "Sales by ShipDate", [Sales by Ship Date],
        "Transactions Shipped", [Transactions Shipped]
    ),
    NOT ISBLANK ( [Sales by SaleDate] )
        || NOT ISBLANK ( [Sales by ShipDate] )
)
ORDER BY 'Date'[Year Month]

The query should return January and February with the control values shown above.

Step 9: Create a daily comparison chart

1. Add a Line chart.
2. Place Date[Date] on the X-axis.
3. Add [Total Sales] and [Sales by Ship Date] to the Y-axis.
4. Set the title to Sales Recorded versus Sales Shipped.
5. Restrict the displayed range to January and February 2026.

The two lines show the same transaction values on different dates.

Step 10: Test a date slicer

1. Add a slicer using Date[Year Month].
2. Select 2026-02.
3. Confirm [Total Sales] is blank because no sales were recorded in February.
4. Confirm [Sales by Ship Date] is 552 because one January sale shipped in February.

When should this pattern be used?

An inactive relationship is suitable when one date role is the normal reporting path and only selected measures need the alternative role. If report users must filter and compare Sale Date and Ship Date independently at the same time, create separate role-playing date tables with active relationships instead.

Security consideration: row-level security filters propagate through active relationships. Do not assume that USERELATIONSHIP can make an inactive relationship carry an RLS filter.

Knowledge check

1. Why is the SaleDate relationship active?
2. Why must the ShipDate relationship be inactive?
3. What does USERELATIONSHIP change during measure evaluation?
4. Why does February contain shipped sales but no recorded sales?
5. When would two separate Date tables be preferable?

Troubleshooting

Problem	What to check
Power BI refuses to activate the ShipDate relationship.	This is expected. Keep SaleDate active and ShipDate inactive.
Sales by Ship Date matches Total Sales on every daily row.	Confirm USERELATIONSHIP references Sales[ShipDate] and Date[Date].
February does not appear.	Confirm the Date table includes the complete 2026 year and the visual/query includes Sales by Ship Date.
The measure reports that the columns are not part of a relationship.	Create the inactive ShipDate relationship before creating or running the measure.
The relationship line is solid.	Edit the ShipDate relationship and clear Make this relationship active.

Exercise complete

You have modelled two date roles without creating an ambiguous default path. Standard measures follow SaleDate, while shipping measures deliberately activate ShipDate with USERELATIONSHIP.

Completion checklist

• ShipDate contains synthetic dates one to three days after SaleDate.
• SaleDate has the active relationship.
• ShipDate has the inactive dashed relationship.
• January shipped sales equal 21,236.
• February shipped sales equal 552.
• Total shipped sales still equal 21,788.

References

• Microsoft Learn: USERELATIONSHIP function (DAX)
• Microsoft Learn: Active versus inactive relationship guidance