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.