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.

Practice

1. What is the main difference between an active and an inactive relationship in Power BI?

easy

A. Active relationships are used automatically in calculations, inactive require USERELATIONSHIP()

B. Inactive relationships are used automatically, active require USERELATIONSHIP()

C. Both active and inactive relationships work automatically without extra functions

D. Inactive relationships cannot be used in any calculations

Solution

1. Step 1: Understand active relationships

   Active relationships are automatically applied in calculations and visuals without extra effort.

2. Step 2: Understand inactive relationships

   Inactive relationships do not apply automatically; you must use USERELATIONSHIP() in DAX to activate them temporarily.

3. Final Answer:

   Active relationships are used automatically in calculations, inactive require USERELATIONSHIP() -> Option A

4. Quick Check:

   Active = automatic, inactive = USERELATIONSHIP() [OK]

Hint: Active works by default; inactive needs USERELATIONSHIP() [OK]

Common Mistakes:

• Thinking inactive relationships work automatically
• Confusing USERELATIONSHIP() usage
• Believing inactive relationships cannot be used

2. Which of the following is the correct syntax to activate an inactive relationship in a measure?

easy

A. CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date]))

B. CALCULATE(SUM(Sales[Amount]), ACTIVERELATIONSHIP(Sales[Date], Calendar[Date]))

C. SUM(Sales[Amount]) + USERELATIONSHIP(Sales[Date], Calendar[Date])

D. CALCULATE(SUM(Sales[Amount]), RELATIONSHIP(Sales[Date], Calendar[Date]))

Solution

1. Step 1: Recall USERELATIONSHIP() usage

   USERELATIONSHIP() is used inside CALCULATE() to activate an inactive relationship temporarily.

2. Step 2: Check syntax correctness

   CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date])) uses CALCULATE with USERELATIONSHIP and correct column references, matching the correct syntax.

3. Final Answer:

   CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date])) -> Option A

4. Quick Check:

   USERELATIONSHIP inside CALCULATE is correct syntax [OK]

Hint: USERELATIONSHIP() must be inside CALCULATE() [OK]

Common Mistakes:

• Using ACTIVERELATIONSHIP() which does not exist
• Adding USERELATIONSHIP() outside CALCULATE()
• Using RELATIONSHIP() instead of USERELATIONSHIP()

3. Given the following measure:

Sales Amount by Order Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Calendar[Date]))

What will this measure calculate if the active relationship is between Sales[ShipDate] and Calendar[Date]?

medium

A. Sum of Sales Amount filtered by ShipDate

B. Sum of Sales Amount ignoring any date filter

C. Sum of Sales Amount filtered by OrderDate using inactive relationship

D. Error because USERELATIONSHIP cannot be used with OrderDate

Solution

1. Step 1: Identify active and inactive relationships

   The active relationship is Sales[ShipDate] to Calendar[Date]. The relationship Sales[OrderDate] to Calendar[Date] is inactive.

2. Step 2: Understand USERELATIONSHIP effect

   Using USERELATIONSHIP activates the inactive relationship on OrderDate temporarily for this measure.

3. Final Answer:

   Sum of Sales Amount filtered by OrderDate using inactive relationship -> Option C

4. Quick Check:

   USERELATIONSHIP activates inactive relationship [OK]

Hint: USERELATIONSHIP switches to inactive relationship filter [OK]

Common Mistakes:

• Assuming measure uses active ShipDate filter
• Thinking USERELATIONSHIP causes error
• Ignoring inactive relationship usage

4. You wrote this measure:

Total Sales = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[Date], Calendar[Date]))

But it returns the same result as SUM(Sales[Amount]). What is the most likely problem?

medium

A. CALCULATE cannot be used with USERELATIONSHIP

B. The relationship between Sales[Date] and Calendar[Date] is already active

C. The Sales[Date] column does not exist in the table

D. USERELATIONSHIP() is misspelled and causes a syntax error

Solution

1. Step 1: Check relationship status

   If the relationship between Sales[Date] and Calendar[Date] is active, USERELATIONSHIP() has no effect because the active relationship is already used.

2. Step 2: Understand measure behavior

   The measure behaves like SUM(Sales[Amount]) filtered by the active relationship, so results match.

3. Final Answer:

   The relationship between Sales[Date] and Calendar[Date] is already active -> Option B

4. Quick Check:

   USERELATIONSHIP only affects inactive relationships [OK]

Hint: USERELATIONSHIP only changes inactive relationships [OK]

Common Mistakes:

• Assuming USERELATIONSHIP causes syntax error
• Thinking CALCULATE cannot use USERELATIONSHIP
• Ignoring relationship status

5. You have two date columns in Sales: OrderDate and ShipDate. The active relationship is between ShipDate and Calendar[Date]. You want a report showing total sales by OrderDate without changing the active relationship. Which approach is best?

hard

A. Create a new Calendar table for OrderDate and link it actively

B. Change the active relationship to OrderDate and remove ShipDate relationship

C. Use the active relationship and ignore OrderDate column

D. Create a measure using CALCULATE with USERELATIONSHIP(Sales[OrderDate], Calendar[Date])

Solution

1. Step 1: Understand relationship setup

   ShipDate is active; OrderDate relationship is inactive to Calendar[Date]. Changing active relationship affects all reports.

2. Step 2: Use USERELATIONSHIP in measure

   Using CALCULATE with USERELATIONSHIP activates the inactive OrderDate relationship temporarily without changing model.

3. Step 3: Evaluate other options

   Changing active relationship or creating new Calendar table is more complex and less flexible.

4. Final Answer:

   Create a measure using CALCULATE with USERELATIONSHIP(Sales[OrderDate], Calendar[Date]) -> Option D

5. Quick Check:

   USERELATIONSHIP enables alternate date filtering [OK]

Hint: Use USERELATIONSHIP to activate alternate date without model changes [OK]

Common Mistakes:

• Switching active relationship instead of using USERELATIONSHIP
• Ignoring inactive relationship usage
• Creating unnecessary new tables