Role-playing dimensions in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the DAX formula to create a new role-playing dimension table using the CALCULATETABLE function.

Power BI

NewDateRole = CALCULATETABLE(Date, [1])

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

AALL(Date)

BFILTER(Date, Date[Date] > DATE(2020,1,1))

CVALUES(Date[Date])

DSUMMARIZE(Date, Date[Year])

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER limits rows, which is not needed here.

Using ALL(Date) removes filters and creates a full copy of the Date table for the new role.

2fill in blank

medium

Complete the expression to create a relationship between the Sales table and the new Date role-playing dimension.

Power BI

CREATE RELATIONSHIP Sales[OrderDate] [1] DateRole[Date]

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

A->

BIN

CRELATED

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using arrows or functions instead of equality operator.

The correct syntax to define a relationship uses the equality operator == between keys.

3fill in blank

hard

Fix the error in this DAX measure that calculates total sales using the role-playing Date dimension.

Power BI

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

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

AFILTER(DateRole, DateRole[Date] > DATE(2021,1,1))

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

CUSERELATIONSHIP(Sales[ShipDate], DateRole[Date])

DALL(Date)

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong date column or forgetting USERELATIONSHIP.

To use the role-playing dimension for ShipDate, USERELATIONSHIP activates that inactive relationship.

4fill in blank

hard

Fill in the blank to create a measure that calculates sales filtered by the role-playing Date dimension for delivery date.

Power BI

Sales by Delivery = CALCULATE(SUM(Sales[Amount]), [1](Sales[DeliveryDate], DateRole[Date]))

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

AUSERELATIONSHIP

BFILTER

CRELATED

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using FILTER or RELATED instead of USERELATIONSHIP.

USERELATIONSHIP activates the inactive relationship between DeliveryDate and DateRole[Date].

5fill in blank

hard

Fill all three blanks to define a role-playing dimension table with a filter on year and create a measure using it.

Power BI

DateRole = CALCULATETABLE(Date, [1](Date, Date[Year] = 2023))

Sales 2023 Role = CALCULATE(SUM(Sales[Amount]), [2](Sales[OrderDate], DateRole[Date]), [3](DateRole))

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

AFILTER

BUSERELATIONSHIP

CALL

DRELATED

Attempts:

3 left

💡 Hint

Common Mistakes

Using RELATED instead of USERELATIONSHIP or missing ALL to clear filters.

Use FILTER to limit Date to 2023, USERELATIONSHIP to activate the role-playing relationship, and ALL to remove filters on DateRole in CALCULATE.

Practice

1. What is the main purpose of using role-playing dimensions in Power BI data models?

easy

A. To avoid using relationships between tables

B. To create multiple copies of the same table for different reports

C. To store measures separately from dimensions

D. To reuse a single dimension table for multiple roles in the model

Solution

1. Step 1: Understand role-playing dimension concept

   Role-playing dimensions allow one table to serve multiple roles, like Order Date and Ship Date, without duplicating tables.

2. Step 2: Identify the correct purpose

   This reuse simplifies the model and avoids redundancy.

3. Final Answer:

   To reuse a single dimension table for multiple roles in the model -> Option D

4. Quick Check:

   Role-playing dimension = reuse one table [OK]

Hint: Role-playing means reusing one table for different roles [OK]

Common Mistakes:

• Thinking role-playing means duplicating tables
• Confusing measures with dimensions
• Believing it removes relationships

2. Which DAX function is used to activate an inactive relationship in a role-playing dimension scenario?

easy

A. USERELATIONSHIP()

B. RELATED()

C. CALCULATE()

D. FILTER()

Solution

1. Step 1: Recall inactive relationships in role-playing dimensions

   Only one relationship can be active; others are inactive and need special handling.

2. Step 2: Identify the function to activate inactive relationships

   USERELATIONSHIP() activates an inactive relationship inside a CALCULATE() expression.

3. Final Answer:

   USERELATIONSHIP() -> Option A

4. Quick Check:

   Activate inactive relationship = USERELATIONSHIP() [OK]

Hint: Use USERELATIONSHIP() to activate inactive links [OK]

Common Mistakes:

• Using RELATED() which only fetches related values
• Confusing CALCULATE() as the activator alone
• Trying FILTER() to activate relationships

3. Given a Sales table linked to a Date table twice: once by OrderDate (active) and once by ShipDate (inactive). What does this DAX measure return?

Shipped Sales = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

medium

A. Sum of sales filtered by ShipDate

B. Error due to inactive relationship

C. Sum of all sales ignoring dates

D. Sum of sales filtered by OrderDate

Solution

1. Step 1: Understand the measure components

   The measure sums Sales[Amount] but uses USERELATIONSHIP() to activate the inactive ShipDate relationship.

2. Step 2: Determine the filter context

   This means the sum is filtered by ShipDate, not the active OrderDate relationship.

3. Final Answer:

   Sum of sales filtered by ShipDate -> Option A

4. Quick Check:

   USERELATIONSHIP activates ShipDate filter [OK]

Hint: USERELATIONSHIP switches filter to inactive date [OK]

Common Mistakes:

• Assuming active relationship always applies
• Thinking it sums all sales without filter
• Expecting an error from inactive relationship

4. You wrote this measure but it returns incorrect results:

Shipped Sales = CALCULATE(SUM(Sales[Amount]), Date[Date])

What is the likely problem?

medium

A. SUM() cannot be used inside CALCULATE()

B. Missing USERELATIONSHIP() to activate inactive ShipDate relationship

C. ShipDate table does not exist

D. CALCULATE() requires a filter expression, not a column

Solution

1. Step 1: Analyze the measure syntax

   The measure tries to filter by Date[Date] but does not activate the inactive relationship.

2. Step 2: Identify missing function for inactive relationship

   Without USERELATIONSHIP(), the inactive relationship is ignored, causing wrong results.

3. Final Answer:

   Missing USERELATIONSHIP() to activate inactive ShipDate relationship -> Option B

4. Quick Check:

   Inactive relationship needs USERELATIONSHIP() [OK]

Hint: Inactive relationships need USERELATIONSHIP() inside CALCULATE() [OK]

Common Mistakes:

• Thinking CALCULATE() can't use SUM()
• Assuming ShipDate table is missing
• Using a column instead of a filter expression

5. You have a Date table linked twice to Sales: OrderDate (active) and DeliveryDate (inactive). You want a measure to calculate total sales for deliveries in 2023 only. Which DAX measure correctly does this?

hard

A. Delivery Sales 2023 = SUM(Sales[Amount]) + FILTER(Date, Date[Year] = 2023)

B. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), Date[Year] = 2023)

C. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023)

D. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Date[Date]), Date[Year] = 2023)

Solution

1. Step 1: Activate the correct inactive relationship

   Since DeliveryDate is inactive, USERELATIONSHIP(Sales[DeliveryDate], Date[Date]) is needed to filter by delivery dates.

2. Step 2: Apply the year filter correctly

   Adding Date[Year] = 2023 filters the Date table to year 2023, restricting sales to that year.

3. Final Answer:

   Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023) -> Option C

4. Quick Check:

   Activate DeliveryDate + filter year 2023 = Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023) [OK]

Hint: Use USERELATIONSHIP with year filter inside CALCULATE() [OK]

Common Mistakes:

• Forgetting USERELATIONSHIP for inactive DeliveryDate
• Filtering only by year without activating relationship
• Using wrong relationship (OrderDate instead of DeliveryDate)