Data model best practices in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a relationship between two tables in Power BI.

Power BI

Model = CREATE_RELATIONSHIP('Sales'[CustomerID], [1])

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

A'Customers'[OrderID]

B'Products'[ProductID]

C'Sales'[ProductID]

D'Customers'[CustomerID]

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing columns from unrelated tables.

Using columns that don't match in data type.

To create a relationship, you connect the key columns from related tables. Here, 'Sales'[CustomerID] relates to 'Customers'[CustomerID].

2fill in blank

medium

Complete the DAX formula to calculate total sales amount.

Power BI

TotalSales = SUM([1])

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

A'Sales'[SalesAmount]

B'Customers'[CustomerID]

C'Sales'[Quantity]

D'Products'[Price]

Attempts:

3 left

💡 Hint

Common Mistakes

Summing quantity instead of sales amount.

Using columns from unrelated tables.

The SUM function should be applied to the 'SalesAmount' column to get total sales.

3fill in blank

hard

Fix the error in the DAX formula to calculate average sales per customer.

Power BI

AverageSales = DIVIDE(SUM('Sales'[SalesAmount]), [1])

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

ACOUNTROWS('Products')

BCOUNT('Customers'[CustomerID])

CDISTINCTCOUNT('Sales'[CustomerID])

DSUM('Sales'[Quantity])

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNTROWS on unrelated tables.

Counting total quantity instead of customers.

To find average sales per customer, divide total sales by the number of unique customers using DISTINCTCOUNT.

4fill in blank

hard

Fill both blanks to create a calculated column that flags high sales orders.

Power BI

HighSalesFlag = IF('Sales'[SalesAmount] [1] 1000, [2], "No")

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

A>

B"Yes"

C<

D"High"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operator.

Returning a number instead of text.

The IF statement checks if SalesAmount is greater than 1000, then returns "Yes", else "No".

5fill in blank

hard

Fill all three blanks to create a measure that calculates sales growth percentage.

Power BI

SalesGrowth = DIVIDE(SUM('Sales'[SalesAmount]) - CALCULATE(SUM('Sales'[SalesAmount]), [1]), CALCULATE(SUM('Sales'[SalesAmount]), [2])) * 100

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

ASAMEPERIODLASTYEAR('Date'[Date])

CFILTER('Date', 'Date'[Year] = YEAR(TODAY()) - 1)

DFILTER('Date', 'Date'[Year] = YEAR(TODAY()))

Attempts:

3 left

💡 Hint

Common Mistakes

Using current year instead of last year for comparison.

Not subtracting last year's sales correctly.

This formula calculates the difference between current sales and last year's sales, then divides by last year's sales to get growth percentage. SAMEPERIODLASTYEAR and FILTER functions help select last year's dates.