Filtering rows in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does filtering rows mean in Power BI?

Filtering rows means selecting only the data that meets certain conditions, so you see just the information you need.

beginner

How do you apply a filter to a table visual in Power BI?

You drag a field to the Filters pane and set the condition, like choosing specific values or ranges to show only matching rows.

beginner

What is a slicer in Power BI?

A slicer is a visual filter that lets users click buttons or select items to filter data on the report page easily.

intermediate

Write a simple DAX formula to filter rows where Sales are greater than 1000.

FILTER(SalesTable, SalesTable[Sales] > 1000)

beginner

Why is filtering rows important in reports?

Filtering rows helps focus on relevant data, making reports clearer and easier to understand, like looking only at your favorite items in a store.

Which Power BI feature lets you filter data by clicking buttons on the report?

A Calculated Column

B Measure

C Slicer

D Data Model

A slicer is a visual filter that allows users to filter data interactively by clicking options.

What does the FILTER function in DAX do?

A Changes the color of a visual

B Creates a new table with rows that meet a condition

C Adds a new column to a table

D Deletes rows from the data source

FILTER returns a table containing only rows that satisfy the given condition.

Where do you set filters for a visual in Power BI Desktop?

A Filters pane

B Data view

C Model view

D Bookmarks pane

The Filters pane is where you add and adjust filters for visuals.

If you want to see only sales from 2023, what type of filter would you use?

A Date filter

B Text filter

C Numeric filter

D No filter needed

A date filter lets you select data from a specific year like 2023.

Which of these is NOT a way to filter rows in Power BI?

A Using slicers

B Applying filters in the Filters pane

C Writing DAX FILTER expressions

D Changing the data source file format

Changing the data source file format does not filter rows in Power BI.

Explain how filtering rows helps improve a Power BI report.

Think about how you choose only what you want to see in a list.

Describe three ways to filter rows in Power BI.

One way is visual, one is interactive, and one is formula-based.

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