Active vs inactive relationships in Power BI - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the DAX formula to calculate total sales using the active relationship.

Power BI

Total Sales = CALCULATE(SUM(Sales[Amount]), [1])

Drag options to blanks, or click blank then click option'

AUSERELATIONSHIP(Date[Date], Sales[OrderDate])

BALL(Date)

CFILTER(Sales, Sales[Amount] > 0)

DRELATED(Date[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using USERELATIONSHIP when the active relationship is already in use.

Using ALL removes filters and breaks the calculation.

The active relationship is used by default in CALCULATE, so no need to specify USERELATIONSHIP. Using FILTER keeps the active relationship context.

2fill in blank

medium

Complete the DAX formula to activate an inactive relationship between Sales and Date tables.

Power BI

Total Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), [1])

Drag options to blanks, or click blank then click option'

AUSERELATIONSHIP(Date[Date], Sales[ShipDate])

BFILTER(Sales, Sales[ShipDate] <> BLANK())

CALL(Sales)

DRELATED(Date[Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER instead of USERELATIONSHIP.

Using ALL removes filters and breaks the calculation.

USERELATIONSHIP activates an inactive relationship inside CALCULATE.

3fill in blank

hard

Fix the error in the DAX formula that tries to use an inactive relationship without USERELATIONSHIP.

Power BI

Total Sales by Ship Date = CALCULATE(SUM(Sales[Amount]), [1])

Drag options to blanks, or click blank then click option'

AFILTER(Date, Date[Date] = Sales[ShipDate])

BRELATED(Sales[ShipDate])

CALL(Date)

DUSERELATIONSHIP(Date[Date], Sales[ShipDate])

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to filter tables without activating the relationship.

Using RELATED incorrectly in this context.

To use an inactive relationship in CALCULATE, you must specify USERELATIONSHIP.

4fill in blank

hard

Fill both blanks to calculate sales using the inactive relationship and filter only orders after 2020.

Power BI

Total Sales After 2020 = CALCULATE(SUM(Sales[Amount]), [1], [2])

Drag options to blanks, or click blank then click option'

AUSERELATIONSHIP(Date[Date], Sales[ShipDate])

BFILTER(Sales, Sales[OrderDate] > DATE(2020,1,1))

CFILTER(Sales, Sales[ShipDate] > DATE(2020,1,1))

DALL(Date)

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering by OrderDate instead of ShipDate.

Not activating the inactive relationship.

USERELATIONSHIP activates the inactive relationship on ShipDate, and FILTER limits sales to ShipDate after 2020.

5fill in blank

hard

Fill all three blanks to create a measure that sums sales amount using the inactive relationship, filters by ShipDate after 2020, and ignores other filters on Date.

Power BI

Total Sales Custom = CALCULATE(SUM(Sales[Amount]), [1], [2], [3])

Drag options to blanks, or click blank then click option'

AUSERELATIONSHIP(Date[Date], Sales[ShipDate])

BFILTER(Sales, Sales[ShipDate] > DATE(2020,1,1))

CALL(Date)

DFILTER(Sales, Sales[OrderDate] > DATE(2020,1,1))

Attempts:

3 left

💡 Hint

Common Mistakes

Filtering by OrderDate instead of ShipDate.

Not removing other filters on Date with ALL.

Not activating the inactive relationship.

USERELATIONSHIP activates the inactive relationship, FILTER limits sales by ShipDate after 2020, and ALL(Date) removes other filters on Date to avoid conflicts.