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.