Filtering rows in Power BI - Step-by-Step Guide

Introduction

Filtering rows lets you show only the data you want in your report. It helps focus on specific information by hiding rows that don't match your needs.

When you want to see sales data for only one region, like West, without other regions.

When your report should display data for a specific year, such as 2023, excluding other years.

When you need to analyze customers who spent more than $1000.

When you want to hide incomplete or test data rows from your visuals.

When you want to create a report page that shows only active products.

Steps

Step 1: Click

- Filters pane on the right side

The Filters pane expands showing filter options for the selected visual or page

Step 2: Select

- Field you want to filter from the Fields list

The field appears in the Filters pane under Visual level filters or Page level filters

Step 3: Choose filter type

- Filters pane under the selected field

Filter controls appear, such as Basic filtering, Advanced filtering, Top N, or Relative date filtering

Step 4: Set filter condition

- Filters pane filter controls

The visual updates to show only rows matching the filter condition

💡 Use Basic filtering to select specific values or Advanced filtering to set conditions like greater than or contains

Step 5: Apply filter

- Filters pane

The report visual or page shows only the filtered rows

Before vs After

Before

A sales table visual shows 1000 rows with all regions and years mixed

After

The sales table visual shows 200 rows only for the West region in 2023

Settings Reference

Filter type

📍 Filters pane under the selected field

Choose how to specify which rows to keep or hide

Default: Basic filtering

Filter scope

📍 Filters pane top section

Decide if the filter applies to one visual, the whole page, or the entire report

Default: Visual level filter

Show items when the value

📍 Advanced filtering options

Set the condition to filter rows based on field values

Default: is

Common Mistakes

Applying filter to the wrong level (visual instead of page or report)

The filter only affects one visual, so other visuals still show unfiltered data

Choose the correct filter scope in the Filters pane to apply the filter where needed

Using Basic filtering when Advanced filtering is needed

Basic filtering only selects specific values and cannot handle conditions like greater than

Switch to Advanced filtering to set conditions like 'greater than 1000'

Summary

Filtering rows helps focus your report on specific data by hiding unwanted rows.

You can filter at visual, page, or report level depending on your needs.

Choose the right filter type and scope to get accurate results.

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