Filtering rows in Power BI - Dashboard Guide

Dashboard Mode - Filtering rows

Business Question

Which sales transactions happened in the East region and had sales above $150?

Sample Data

Transaction ID	Region	Sales	Product
1	East	100	Widget A
2	West	200	Widget B
3	East	250	Widget C
4	North	300	Widget A
5	East	180	Widget B
6	South	90	Widget C
7	West	220	Widget A

Dashboard Components

• KPI Card: Total Sales in East Region Above $150
  Formula:
  Total Sales Filtered = CALCULATE(SUM(Sales[Sales]), Sales[Region] = "East", Sales[Sales] > 150)
  Result: 430 (250 + 180)
• Table: Filtered Transactions
  Shows only rows where Region = East and Sales > 150.
  Rows:
  Transaction ID 3, Region East, Sales 250, Product Widget C
  Transaction ID 5, Region East, Sales 180, Product Widget B
• Bar Chart: Sales by Product (Filtered)
  Shows sales for products only in filtered rows.
  Data:
  Widget C: 250
  Widget B: 180

Dashboard Layout

+-----------------------------+-----------------------------+
|        KPI Card             |        Bar Chart             |
|  Total Sales Filtered       |  Sales by Product            |
+-----------------------------+-----------------------------+
|                         Filtered Transactions Table                 |
+-------------------------------------------------------------------+

Interactivity

The filter for Region = East and Sales > 150 is applied to all components.

When the filter changes, the KPI card, table, and bar chart update automatically to show only the filtered data.

Self Check

If you add a filter for Product = Widget B, which components update and what do they show?

• KPI Card updates to show total sales for East region, sales > 150, and product Widget B: 180
• Table updates to show only transaction ID 5
• Bar Chart updates to show sales only for Widget B: 180

Key Result

Dashboard shows sales transactions filtered by East region and sales above $150 with total sales KPI, filtered table, and product sales bar chart.

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