FILTER function in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to filter the Sales table for sales greater than 1000.

Power BI

FILTER(Sales, Sales[Amount] [1] 1000)

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

A<

B>

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will filter for sales less than 1000.

Using '=' will filter only sales exactly equal to 1000.

The FILTER function requires a condition. To get sales greater than 1000, use the '>' operator.

2fill in blank

medium

Complete the code to filter the Customers table for customers in the '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 '<>' will filter customers not in the USA.

Using '>' or '<' does not make sense for text comparison here.

To filter for customers in the USA, use the '=' operator to check equality.

3fill in blank

hard

Fix the error in the FILTER function to correctly filter products with stock less than 50.

Power BI

FILTER(Products, Products[Stock] [1] 50)

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

A<

B=

C>=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' will filter products with stock greater than 50.

Using '=' will filter products with stock exactly 50.

To filter products with stock less than 50, use the '<' operator.

4fill in blank

hard

Fill both blanks to filter the Orders table for orders with quantity greater than 10 and status 'Completed'.

Power BI

FILTER(Orders, Orders[Quantity] [1] 10 && Orders[Status] [2] "Completed")

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

A>

B<

C=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

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

Using '<>' instead of '=' for status.

The condition requires quantity greater than 10 and status equal to 'Completed'. Use '>' and '=' respectively.

5fill in blank

hard

Fill all three blanks to filter the Employees table for employees in 'Sales' department with salary greater than 50000 and active status.

Power BI

FILTER(Employees, Employees[Department] [1] "Sales" && Employees[Salary] [2] 50000 && Employees[Active] [3] TRUE())

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

A=

B>

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<>' instead of '=' for department or active status.

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

We check department equals 'Sales' (use '='), salary greater than 50000 (use '>'), and active status equals TRUE (use '=').

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