FILTER function in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

FILTER function with simple condition

Given the table Sales with columns Product and Quantity, what is the result of this DAX expression?

FILTER(Sales, Sales[Quantity] > 10)

A Returns all rows where Quantity is greater than 10

B Returns all rows where Quantity is less than or equal to 10

C Returns a single value of Quantity greater than 10

D Returns an error because FILTER needs a measure

💡 Hint

FILTER returns a table with rows matching the condition.

FILTER returns a table containing only rows where the condition is true. Here, rows with Quantity > 10 are included.

❓ visualization

intermediate

Using FILTER in a measure for visualization

You want to create a card visualization showing total sales for products with sales greater than 100 units. Which DAX measure using FILTER is correct?

A TotalSalesFiltered = SUM(FILTER(Sales, Sales[Quantity] > 100)[Amount])

B TotalSalesFiltered = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Quantity] > 100))

C TotalSalesFiltered = FILTER(Sales, SUM(Sales[Quantity]) > 100)

D TotalSalesFiltered = CALCULATE(FILTER(Sales, Sales[Quantity] > 100), SUM(Sales[Amount]))

💡 Hint

Use CALCULATE with FILTER to modify filter context.

Option B correctly uses CALCULATE with FILTER to sum Amount only for rows where Quantity > 100. Other options misuse FILTER or aggregation.

❓ data_modeling

advanced

FILTER function with related tables

You have two tables: Orders and Customers, related by CustomerID. You want to filter Orders for customers in the 'USA'. Which DAX FILTER expression is correct?

A FILTER(Orders, Orders[Country] = "USA")

B FILTER(Orders, Customers[Country] = "USA")

C FILTER(Orders, RELATED(Customers[Country]) = "USA")

D FILTER(Customers, Customers[Country] = "USA")

💡 Hint

Use RELATED to access columns from related tables in FILTER.

Option C uses RELATED to get the Country from Customers table for each Order row. Option C is invalid because Customers is a different table, C uses wrong column, D filters Customers not Orders.

🔧 Formula Fix

advanced

Identify the error in this FILTER expression

What error will this DAX expression cause?

FILTER(Sales, Sales[Quantity] > 10 && Sales[Date])

A Type error because Sales[Date] is not a boolean

B No error, returns filtered table

C Runtime error due to missing aggregation

D Syntax error due to invalid logical condition

💡 Hint

Check the condition inside FILTER carefully.

The condition uses '&& Sales[Date]' which is invalid because Sales[Date] is a date, not a boolean expression. This causes a syntax error.

🧠 Conceptual

expert

FILTER function behavior with empty tables

If FILTER is applied on an empty table, what is the result?

A Returns an empty table

B Returns a table with one row containing blanks

C Returns an error because table is empty

D Returns the original table unfiltered

💡 Hint

Think about how FILTER works on no rows.

FILTER returns a table with only rows that meet the condition. If the input table is empty, no rows exist to filter, so the result is an empty table.

Practice

1. What does the FILTER function do in Power BI?

easy

A. It calculates the sum of a column.

B. It creates a new table by combining two tables.

C. It selects rows from a table that meet a specific condition.

D. It changes the data type of a column.

Solution

1. Step 1: Understand the purpose of FILTER

   The FILTER function is designed to pick rows from a table based on a condition you specify.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions like JOIN, SUM, or data type changes, which FILTER does not do.

3. Final Answer:

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

4. Quick Check:

   FILTER picks rows = A [OK]

Hint: FILTER always returns rows matching a condition [OK]

Common Mistakes:

• Thinking FILTER changes data types
• Confusing FILTER with aggregation functions
• Assuming FILTER combines tables

2. Which of the following is the correct syntax for the FILTER function in DAX?

easy

A. FILTER(Table; Condition)

B. FILTER(Table, Condition)

C. FILTER(Condition, Table)

D. FILTER[Table, Condition]

Solution

1. Step 1: Recall FILTER syntax

   The correct syntax is FILTER(table, condition) where the first argument is the table and the second is the condition.

2. Step 2: Check each option

   FILTER(Table, Condition) matches the correct syntax. FILTER(Condition, Table) reverses arguments. FILTER(Table; Condition) uses a semicolon instead of a comma. FILTER[Table, Condition] uses brackets incorrectly.

3. Final Answer:

   FILTER(Table, Condition) -> Option B

4. Quick Check:

   FILTER syntax = FILTER(table, condition) [OK]

Hint: Table first, condition second in FILTER [OK]

Common Mistakes:

• Swapping table and condition order
• Using semicolons instead of commas
• Using brackets instead of parentheses

3. Given the table Sales with a column Amount, what does this DAX expression return?

FILTER(Sales, Sales[Amount] > 100)

medium

A. All rows where Amount is greater than 100

B. A single value showing the max Amount

C. Sum of Amount values greater than 100

D. All rows where Amount is less than or equal to 100

Solution

1. Step 1: Analyze the FILTER condition

   The condition Sales[Amount] > 100 means only rows with Amount greater than 100 are kept.

2. Step 2: Understand FILTER output

   FILTER returns a table with rows meeting the condition, not sums or single values.

3. Final Answer:

   All rows where Amount is greater than 100 -> Option A

4. Quick Check:

   FILTER with Amount > 100 returns matching rows [OK]

Hint: FILTER returns rows matching condition, not aggregates [OK]

Common Mistakes:

• Thinking FILTER returns sums or single values
• Confusing > with <=
• Assuming FILTER changes data

4. Identify the error in this DAX expression:

FILTER(Sales, Sales[Amount] => 50)

medium

A. The comparison operator => is invalid

B. The table name is incorrect

C. Missing closing parenthesis

D. FILTER requires three arguments

Solution

1. Step 1: Check the comparison operator

   The operator => is not valid in DAX; the correct operator for 'greater than or equal' is >=.

2. Step 2: Verify other parts

   The table name Sales is correct, parentheses are balanced, and FILTER takes two arguments, so no other errors.

3. Final Answer:

   The comparison operator => is invalid -> Option A

4. Quick Check:

   Use >=, not => for comparisons [OK]

Hint: Use >= for 'greater or equal', not => [OK]

Common Mistakes:

• Using => instead of >=
• Assuming FILTER needs more arguments
• Ignoring syntax errors in operators

5. You want to calculate the total sales amount only for products in the 'Electronics' category. Which DAX measure correctly uses FILTER to do this?

Options:
A) TotalElectronics = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Electronics"))
B) TotalElectronics = SUM(FILTER(Sales, Sales[Category] = "Electronics"))
C) TotalElectronics = FILTER(Sales, Sales[Category] = "Electronics")
D) TotalElectronics = CALCULATE(FILTER(Sales, Sales[Category] = "Electronics"), SUM(Sales[Amount]))

hard

A. TotalElectronics = CALCULATE(FILTER(Sales, Sales[Category] = "Electronics"), SUM(Sales[Amount]))

B. TotalElectronics = SUM(FILTER(Sales, Sales[Category] = "Electronics"))

C. TotalElectronics = FILTER(Sales, Sales[Category] = "Electronics")

D. TotalElectronics = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Electronics"))

Solution

1. Step 1: Understand the goal

   You want the sum of Amount but only for rows where Category is 'Electronics'.

2. Step 2: Analyze each option

   TotalElectronics = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Electronics")) uses CALCULATE with SUM and FILTER correctly to filter rows before summing.
   TotalElectronics = SUM(FILTER(Sales, Sales[Category] = "Electronics")) tries to SUM a table, which is invalid.
   TotalElectronics = FILTER(Sales, Sales[Category] = "Electronics") returns a table, not a sum.
   TotalElectronics = CALCULATE(FILTER(Sales, Sales[Category] = "Electronics"), SUM(Sales[Amount])) misuses CALCULATE arguments order.

3. Final Answer:

   TotalElectronics = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Category] = "Electronics")) -> Option D

4. Quick Check:

   Use CALCULATE with FILTER for conditional sums [OK]

Hint: Use CALCULATE with FILTER to sum filtered rows [OK]

Common Mistakes:

• Trying to SUM a filtered table directly
• Returning a table instead of a number
• Incorrect argument order in CALCULATE