Active vs inactive relationships in Power BI - Practice Questions

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Measure Using Inactive Relationship

Given two tables, Sales and Dates, connected by an active relationship on OrderDate and an inactive relationship on ShipDate, which DAX measure correctly calculates total sales based on ShipDate?

Power BI

Total Sales by ShipDate = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Dates[Date]))

A Total Sales by ShipDate = SUM(Sales[Amount])

B Total Sales by ShipDate = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Dates[Date]))

C Total Sales by ShipDate = CALCULATE(SUM(Sales[Amount]), ALL(Dates))

D Total Sales by ShipDate = CALCULATE(SUM(Sales[Amount]), FILTER(Dates, Dates[Date] = Sales[ShipDate]))

💡 Hint

Use USERELATIONSHIP to activate an inactive relationship in a measure.

The inactive relationship between Sales[ShipDate] and Dates[Date] is not used by default. The USERELATIONSHIP function activates it temporarily inside the CALCULATE function, allowing the measure to filter by ShipDate.

❓ visualization

intermediate

Visualizing Active vs Inactive Relationship Impact

You have a report page with two cards showing total sales: one filtered by OrderDate (active relationship) and one by ShipDate (inactive relationship). Which visualization setup best shows the difference clearly and accessibly?

A A table listing sales amounts with no headers or titles.

B One card showing total sales without specifying date type, with a small legend explaining dates.

C A pie chart combining sales by OrderDate and ShipDate in one slice each, without labels.

D Two cards side by side with clear titles: 'Sales by Order Date' and 'Sales by Ship Date', using contrasting colors and large fonts.

💡 Hint

Clear labeling and contrast help users understand differences quickly.

Two cards side by side with clear titles and contrasting colors make it easy to compare sales by active and inactive relationships. Accessibility is improved by large fonts and clear labels.

🧠 Conceptual

advanced

Understanding Relationship Activation in Power BI

Which statement best explains the difference between active and inactive relationships in Power BI data models?

A Active relationships filter data by default; inactive relationships require explicit activation in DAX to filter data.

B Inactive relationships automatically filter data unless overridden by active relationships.

C Active relationships cannot be deactivated once created; inactive relationships are deleted relationships.

D Inactive relationships are used only for visual-level filters and cannot be used in measures.

💡 Hint

Think about which relationships apply filters automatically.

Active relationships apply filters automatically in visuals and calculations. Inactive relationships exist in the model but do not filter data unless explicitly activated using functions like USERELATIONSHIP in DAX.

🔧 Formula Fix

advanced

Debugging a Measure Using Inactive Relationship

A measure intended to calculate sales by ShipDate is returning total sales by OrderDate instead. What is the most likely cause?

A The measure does not use USERELATIONSHIP to activate the inactive ShipDate relationship.

B The ShipDate column is missing from the Sales table.

C The Dates table has no active relationship with Sales.

D The measure uses CALCULATE but SUM is missing.

💡 Hint

Inactive relationships need special handling in DAX.

If the measure does not include USERELATIONSHIP to activate the inactive ShipDate relationship, the active OrderDate relationship is used by default, causing the measure to filter by OrderDate instead.

🎯 Scenario

expert

Scenario: Combining Active and Inactive Relationships in a Report

You want to create a report showing total sales by OrderDate and total sales by ShipDate side by side. The data model has an active relationship on OrderDate and an inactive relationship on ShipDate. Which approach will produce correct results for both measures in the same report?

A Use a slicer to switch between OrderDate and ShipDate relationships dynamically.

B Activate the ShipDate relationship in the model and deactivate the OrderDate relationship.

C Create two measures: one summing sales normally (uses active OrderDate), and one using CALCULATE with USERELATIONSHIP to activate ShipDate; then place both in the report.

D Create one measure using USERELATIONSHIP for ShipDate and use it for both visuals.

💡 Hint

You can have multiple measures using different relationships simultaneously.

Because only one relationship can be active at a time, keep OrderDate active for normal filtering. Use USERELATIONSHIP in a separate measure to activate the inactive ShipDate relationship when needed. Both measures can then be shown side by side.

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