Filtering rows in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see sales data only for the 'East' region to understand its performance.

📊 Data: You have a sales table with columns: OrderID, Region, Product, Quantity, and SalesAmount.

🎯 Deliverable: Create a filtered report or table showing only the rows where Region is 'East'.

Sample Data

OrderID	Region	Product	Quantity	SalesAmount
1001	East	Chair	5	500
1002	West	Table	3	450
1003	East	Lamp	7	210
1004	North	Sofa	2	1200
1005	East	Desk	4	800
1006	South	Chair	6	600
1007	West	Lamp	1	30
1008	East	Sofa	3	1800

1

Step 1: Open Power BI Desktop and load the sales data table.

Import the data from your source file or database.

Expected Result

Sales data table with all rows loaded into Power BI.

2

Step 2: Create a new table visual on the report canvas.

Add columns: OrderID, Region, Product, Quantity, SalesAmount to the table visual.

Expected Result

Table visual showing all sales records.

3

Step 3: Apply a filter to show only rows where Region equals 'East'.

In the Filters pane, drag the Region field to Filters on this visual, then select only 'East'.

Expected Result

Table visual updates to show only sales records from the East region.

4

Step 4: Verify the filtered data matches only East region rows.

Check that all rows in the table have Region = 'East'.

Expected Result

Filtered table shows 4 rows with OrderIDs 1001, 1003, 1005, and 1008.

Final Result

Filtered Sales Table (Region = East)

+---------+--------+---------+----------+-------------+
| OrderID | Region | Product | Quantity | SalesAmount |
+---------+--------+---------+----------+-------------+
| 1001    | East   | Chair   | 5        | 500         |
| 1003    | East   | Lamp    | 7        | 210         |
| 1005    | East   | Desk    | 4        | 800         |
| 1008    | East   | Sofa    | 3        | 1800        |
+---------+--------+---------+----------+-------------+

✓The East region has 4 sales orders in the data.

✓Total sales amount for East region is 3310 (500 + 210 + 800 + 1800).

✓Filtering rows helps focus analysis on specific regions easily.

Bonus Challenge

Create a measure that calculates total sales only for the East region without filtering the visual.

Show Hint

Use DAX formula: TotalSalesEast = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "East")

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