Filtering rows in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Filter with CALCULATE and FILTER

Given a Sales table with columns Product and SalesAmount, what is the result of this DAX measure?

Filtered Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Product] = "Bike"))

Assuming the Sales table has these rows:

• Bike, 100
• Car, 200
• Bike, 150
• Boat, 300

What is the value of Filtered Sales?

Power BI

Filtered Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Product] = "Bike"))

A 250

B 750

C 450

D 100

💡 Hint

Only sum the SalesAmount where Product is exactly "Bike".

The FILTER function limits rows to those where Product is "Bike". The SUM then adds 100 + 150 = 250.

❓ visualization

intermediate

Best Visualization for Filtering by Date Range

You want to create a report page where users can filter sales data by a date range interactively. Which visualization component is best suited for this purpose?

A Stacked bar chart showing sales by product

B Slicer with a date range slider

C Card visual showing total sales

D Table visual listing all sales transactions

💡 Hint

Think about which visual lets users pick a range of dates easily.

A slicer with a date range slider allows users to select start and end dates to filter the report dynamically.

❓ data_modeling

advanced

Filtering Rows in a Related Table

You have two tables: Orders and Customers. Orders has a CustomerID column related to Customers. You want to create a measure that sums Order Amount only for customers in the "Premium" segment. Which DAX expression correctly filters Orders based on Customers segment?

A CALCULATE(SUM(Orders[OrderAmount]), Orders[Customers[Segment]] = "Premium")

B SUM(Orders[OrderAmount]) FILTER(Customers, Customers[Segment] = "Premium")

C SUMX(FILTER(Orders, RELATED(Customers[Segment]) = "Premium"), Orders[OrderAmount])

D CALCULATE(SUM(Orders[OrderAmount]), FILTER(Customers, Customers[Segment] = "Premium"))

💡 Hint

Use RELATED to access columns from the related Customers table inside FILTER on Orders.

Option C filters Orders rows where the related Customer's Segment is "Premium", then sums OrderAmount. Other options have syntax errors or incorrect filter context.

🔧 Formula Fix

advanced

Identify the Error in DAX Filter Expression

What error will this DAX expression cause?

Filtered Sales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Product] == "Bike"))

A SyntaxError due to '==' operator

B No error, returns sum of Bike sales

C TypeError because SalesAmount is not numeric

D Filter returns empty table, so result is blank

💡 Hint

Check the comparison operator used in DAX.

DAX uses a single '=' for equality, not '=='. Using '==' causes a syntax error.

🧠 Conceptual

expert

Effect of Row-Level Security on Filtering

In a Power BI report with Row-Level Security (RLS) applied to the Sales table, what happens when you apply an additional filter on Sales[Region] = "West" in a visual?

A The visual shows no data because filters conflict

B The visual ignores RLS and shows all West region sales

C The visual shows all sales the user can see, ignoring the West filter

D The visual shows only sales in the West region that the user is allowed to see per RLS

💡 Hint

Think about how RLS and report filters combine.

RLS filters data first, then report filters apply. So the visual shows data allowed by RLS and further filtered by Region = "West".

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