Why clean data drives accurate reports in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to remove duplicate rows from the dataset.

Power BI

CleanData = DISTINCT([1])

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

AFILTER

BSales

CSUMMARIZE

DCALCULATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function name instead of a table name.

Confusing DISTINCT with aggregation functions.

The DISTINCT function removes duplicate rows from the specified table. Here, we apply it to the 'Sales' table to clean duplicates.

2fill in blank

medium

Complete the DAX formula to calculate the total sales after filtering out blank values.

Power BI

TotalSales = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] [1] BLANK()))

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

A=

B<

C<>

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which would keep only blank values.

Using '>' or '<' which may exclude valid zero or positive values.

The filter condition uses '<>' to exclude blank values from the 'Amount' column, ensuring only valid sales are summed.

3fill in blank

hard

Fix the error in the DAX formula to calculate average sales correctly.

Power BI

AverageSales = AVERAGE([1][Amount])

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

ASalesData

BAmount

CSalesTable

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong or non-existent table name.

Using the column name alone without table reference.

The correct table name is 'Sales'. Using 'Sales[Amount]' correctly references the column for averaging.

4fill in blank

hard

Fill both blanks to create a calculated column that flags sales above average.

Power BI

SalesFlag = IF(Sales[Amount] [1] Sales[AverageAmount], [2], "Below Average")

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

A>

B"Above Average"

C"High"

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for comparison.

Returning a wrong string for the true condition.

The IF statement checks if 'Amount' is greater than 'AverageAmount'. If true, it returns 'Above Average', else 'Below Average'.

5fill in blank

hard

Fill all three blanks to create a measure that counts non-blank sales records in 2023.

Power BI

SalesCount2023 = CALCULATE(COUNTROWS([1]), FILTER([2], YEAR([3][Date]) = 2023 && NOT(ISBLANK([3][Amount]))))

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

ASales

DSalesData

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing different table names causing errors.

Not using ISBLANK to exclude blank amounts.

All references should be to the 'Sales' table to count rows where the year is 2023 and the Amount is not blank.