Active vs inactive relationships in Power BI - Business Scenario Comparison

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to compare sales totals using two different relationships between the Sales and Dates tables: one active and one inactive. They want to see how sales differ when filtered by Order Date (active relationship) versus Ship Date (inactive relationship).

📊 Data: You have two tables: Sales and Dates. Sales has OrderDate, ShipDate, and SalesAmount. Dates has Date and Month. There are two relationships between Sales and Dates: one active on OrderDate and one inactive on ShipDate.

🎯 Deliverable: Create a report showing total sales by Month using both the active (OrderDate) and inactive (ShipDate) relationships side by side.

Sample Data

SalesID	OrderDate	ShipDate	SalesAmount
1	2024-01-05	2024-01-07	100
2	2024-01-15	2024-01-20	200
3	2024-02-10	2024-02-12	150
4	2024-02-25	2024-03-01	300
5	2024-03-05	2024-03-07	250
6	2024-03-15	2024-03-18	350

Date	Month
2024-01-05	January
2024-01-07	January
2024-01-15	January
2024-01-20	January
2024-02-10	February
2024-02-12	February
2024-02-25	February
2024-03-01	March
2024-03-05	March
2024-03-07	March
2024-03-15	March
2024-03-18	March

1

Step 1: Load the Sales and Dates tables into Power BI. Confirm there are two relationships between Sales and Dates: one active on Sales[OrderDate] to Dates[Date], and one inactive on Sales[ShipDate] to Dates[Date].

In the Model view, check that the active relationship is Sales[OrderDate] -> Dates[Date] and the inactive relationship is Sales[ShipDate] -> Dates[Date].

Expected Result

Two relationships exist: active on OrderDate, inactive on ShipDate.

2

Step 2: Create a measure to calculate total sales using the active relationship (OrderDate).

Total Sales Active = SUM(Sales[SalesAmount])

Expected Result

Total Sales Active sums SalesAmount filtered by Dates[Date] through the active OrderDate relationship.

3

Step 3: Create a measure to calculate total sales using the inactive relationship (ShipDate). Use the USERELATIONSHIP function to activate the inactive relationship in this calculation.

Total Sales Inactive = CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Dates[Date]))

Expected Result

Total Sales Inactive sums SalesAmount filtered by Dates[Date] through the inactive ShipDate relationship.

4

Step 4: Create a table visual in Power BI with Dates[Month] as rows. Add both Total Sales Active and Total Sales Inactive measures as values side by side.

Table visual configuration: Rows = Dates[Month], Values = Total Sales Active, Total Sales Inactive

Expected Result

Table shows sales totals by month using both active and inactive relationships.

5

Step 5: Verify the results by comparing sales totals for each month. Notice differences where ShipDate falls in a different month than OrderDate.

Manually check sums for January, February, and March using sample data.

Expected Result

January: Active=300, Inactive=300; February: Active=450, Inactive=150; March: Active=600, Inactive=900

Final Result

Month      | Total Sales Active | Total Sales Inactive
-----------------------------------------------
January    | 300                | 300
February   | 450                | 150
March      | 600                | 900

✓Sales totals differ when filtered by OrderDate (active) versus ShipDate (inactive).

✓For February, active sales are higher because some sales shipped later in March.

✓For March, inactive sales are higher because some sales ordered in February shipped in March.

Bonus Challenge

Create a slicer to select either OrderDate or ShipDate dynamically and update the sales totals accordingly.

Show Hint

Use a disconnected table with 'OrderDate' and 'ShipDate' options and SWITCH function in a measure to switch USERELATIONSHIP usage.