Filtering rows in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to filter the Sales table for only rows where Quantity is greater than 10.

Power BI

FILTER(Sales, Sales[Quantity] [1] 10)

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

A<

B=

C>

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > which filters the wrong rows.

Using = which only keeps rows where Quantity equals 10.

The FILTER function keeps rows where the condition is true. We want Quantity greater than 10, so we use >.

2fill in blank

medium

Complete the code to filter the Customers table for rows where Country equals 'USA'.

Power BI

FILTER(Customers, Customers[Country] [1] "USA")

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

A<>

B<

C>

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using <> which means 'not equal' and filters out USA.

Using > or < which are for numeric comparisons.

To filter rows where Country is exactly 'USA', use the equals operator =.

3fill in blank

hard

Fix the error in the code to filter the Orders table for rows where OrderDate is after January 1, 2023.

Power BI

FILTER(Orders, Orders[OrderDate] [1] DATE(2023, 1, 1))

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

A>

B=

C<=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using <= which filters dates before or on January 1.

Using = which only filters orders exactly on January 1.

To get orders after January 1, 2023, use the > operator to compare dates.

4fill in blank

hard

Fill both blanks to filter the Products table for rows where Price is between 50 and 100 inclusive.

Power BI

FILTER(Products, Products[Price] [1] 50 && Products[Price] [2] 100)

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

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using > and < which exclude the boundary values.

Mixing up the operators and filtering wrong ranges.

Price should be greater than or equal to 50 and less than or equal to 100 to include both ends.

5fill in blank

hard

Fill all three blanks to filter the Employees table for rows where Department is 'Sales' and Age is greater than 30.

Power BI

FILTER(Employees, Employees[[1]] [2] "[3]" && Employees[Age] > 30)

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

ADepartment

B=

CSales

DPosition

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Position' instead of 'Department' as the column name.

Using <> instead of = which filters out Sales.

Forgetting to put 'Sales' in quotes.

The filter checks if Department equals 'Sales' and Age is greater than 30.

Practice

1.

What does the FILTER function do in Power BI?

easy

A. It selects rows from a table that meet a condition.

B. It creates new columns in a table.

C. It changes the data type of a column.

D. It sorts the table alphabetically.

Solution

1. Step 1: Understand the purpose of FILTER

   The FILTER function is used to pick rows from a table based on a condition.

2. Step 2: Compare options with FILTER's function

   Only It selects rows from a table that meet a condition. describes selecting rows by condition, which matches FILTER's purpose.

3. Final Answer:

   It selects rows from a table that meet a condition. -> Option A

4. Quick Check:

   FILTER picks rows by condition = A [OK]

Hint: FILTER means pick rows that match a rule [OK]

Common Mistakes:

• Confusing FILTER with creating columns
• Thinking FILTER changes data types
• Assuming FILTER sorts data

2.

Which of the following is the correct syntax to filter a table Sales for rows where Amount is greater than 100?

FILTER(____, Amount > 100)

easy

A. FILTER

B. Sales

C. Amount

D. 100

Solution

1. Step 1: Identify the table argument in FILTER

   The first argument of FILTER is the table to filter, here it should be 'Sales'.

2. Step 2: Confirm the condition syntax

   The condition 'Amount > 100' is correct, so the missing part is the table name 'Sales'.

3. Final Answer:

   Sales -> Option B

4. Quick Check:

   FILTER(table, condition) needs table = A [OK]

Hint: First argument in FILTER is the table name [OK]

Common Mistakes:

• Using column name instead of table
• Putting FILTER as first argument
• Using a number instead of table

3.

Given the table Products with columns Category and Price, what will this DAX expression return?

FILTER(Products, Products[Category] = "Beverages" && Products[Price] < 20)

medium

A. All products with Category 'Beverages' and Price less than 20.

B. All products with Category 'Beverages' or Price less than 20.

C. All products with Price less than 20 only.

D. All products with Category 'Beverages' only.

Solution

1. Step 1: Analyze the FILTER condition

   The condition uses && which means AND, so both must be true: Category = "Beverages" AND Price < 20.

2. Step 2: Understand the result of FILTER

   FILTER returns rows where both conditions hold, so only products matching both are included.

3. Final Answer:

   All products with Category 'Beverages' and Price less than 20. -> Option A

4. Quick Check:

   AND condition filters both criteria = B [OK]

Hint: && means both conditions must be true [OK]

Common Mistakes:

• Confusing && (AND) with || (OR)
• Ignoring one condition
• Assuming it filters only by one column

4.

Identify the error in this DAX expression that filters Orders for Quantity greater than 10:

FILTER(Orders, Quantity > 10)

medium

A. The table name Orders is missing.

B. FILTER cannot be used with numeric conditions.

C. Quantity should be referenced as Orders[Quantity].

D. The condition should use < instead of >.

Solution

1. Step 1: Check column reference in condition

   In DAX, columns must be referenced with table name, so 'Quantity' alone is invalid; it should be 'Orders[Quantity]'.

2. Step 2: Confirm other parts are correct

   The table 'Orders' is present, and the condition operator > 10 is valid.

3. Final Answer:

   Quantity should be referenced as Orders[Quantity]. -> Option C

4. Quick Check:

   Column needs table prefix = C [OK]

Hint: Always prefix columns with table name in FILTER [OK]

Common Mistakes:

• Using column name without table prefix
• Thinking FILTER can't use numeric conditions
• Changing operators without reason

5.

You want to create a measure that counts how many sales have PurchaseAmount over $500 in the Sales table. Which DAX expression correctly filters the Sales table?

COUNTROWS(FILTER(Sales, ____))

hard

A. Sales[CustomerID] > 500

B. Sales[CustomerID] < 500

C. Sales[PurchaseAmount] < 500

D. Sales[PurchaseAmount] > 500

Solution

1. Step 1: Identify the filter condition for purchases over $500

   The condition should check if PurchaseAmount is greater than 500 to filter relevant sales.

2. Step 2: Confirm the correct column and operator

   Sales[PurchaseAmount] > 500 uses Sales[PurchaseAmount] > 500, which correctly filters sales over $500.

3. Final Answer:

   Sales[PurchaseAmount] > 500 -> Option D

4. Quick Check:

   Filter sales where PurchaseAmount > 500 = D [OK]

Hint: Filter by purchase amount greater than 500 [OK]

Common Mistakes:

• Filtering by CustomerID instead of PurchaseAmount
• Using less than instead of greater than
• Confusing column names