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.