Page-level filters in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a sales report where users can see sales data filtered by product category on each report page separately.

📊 Data: You have sales data including Date, Product Category, Product Name, Region, and Sales Amount.

🎯 Deliverable: Create a Power BI report with two pages. Each page should have a page-level filter for Product Category to show sales data only for the selected category on that page.

Sample Data

Date	Product Category	Product Name	Region	Sales Amount
2024-01-05	Electronics	Smartphone	North	500
2024-01-10	Electronics	Laptop	South	700
2024-01-15	Furniture	Desk	East	300
2024-01-20	Furniture	Chair	West	150
2024-01-25	Clothing	T-Shirt	North	100
2024-01-30	Clothing	Jeans	South	200
2024-02-05	Electronics	Tablet	East	400
2024-02-10	Furniture	Sofa	West	800
2024-02-15	Clothing	Jacket	North	250
2024-02-20	Electronics	Camera	South	350

1

Step 1: Load the sales data into Power BI Desktop by importing the table with columns Date, Product Category, Product Name, Region, and Sales Amount.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the data.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a new report page named 'Electronics Sales'.

Click the '+' icon at the bottom to add a new page and rename it to 'Electronics Sales'.

Expected Result

A new blank report page named 'Electronics Sales' is created.

3

Step 3: Add a page-level filter for 'Product Category' on the 'Electronics Sales' page and select only 'Electronics'.

In the Filters pane, drag 'Product Category' to 'Filters on this page', then select only 'Electronics'.

Expected Result

The page filter restricts data to only 'Electronics' products on this page.

4

Step 4: Add a visual on the 'Electronics Sales' page showing total sales by Product Name.

Insert a bar chart visual. Set Axis = Product Name, Values = sum of Sales Amount.

Expected Result

Bar chart shows sales amounts for Electronics products only.

5

Step 5: Create a second report page named 'Furniture Sales'.

Add a new page and rename it to 'Furniture Sales'.

Expected Result

A new blank report page named 'Furniture Sales' is created.

6

Step 6: Add a page-level filter for 'Product Category' on the 'Furniture Sales' page and select only 'Furniture'.

Drag 'Product Category' to 'Filters on this page' and select only 'Furniture'.

Expected Result

The page filter restricts data to only 'Furniture' products on this page.

7

Step 7: Add a visual on the 'Furniture Sales' page showing total sales by Region.

Insert a column chart visual. Set Axis = Region, Values = sum of Sales Amount.

Expected Result

Column chart shows sales amounts for Furniture products by region.

8

Step 8: Test the report by switching between pages and verifying that each page shows data only for its filtered Product Category.

Navigate between 'Electronics Sales' and 'Furniture Sales' pages and observe visuals.

Expected Result

Each page shows sales data only for the selected product category due to page-level filters.

Final Result

Bar Chart: Product Name vs Sales Amount

↓

Column Chart: Region vs Sales Amount

✓Page-level filters allow each report page to focus on a specific product category.

✓Electronics sales are strong in North and South regions.

✓Furniture sales are highest in the West region.

Bonus Challenge

Add a third report page with a page-level filter for 'Clothing' and create a line chart showing sales trends over time.

Show Hint

Use the Date column on the X-axis and sum of Sales Amount on the Y-axis. Apply the page-level filter for 'Clothing'.

Practice

1. What is the main purpose of a page-level filter in Power BI?

easy

A. To change the data model structure

B. To filter data for all visuals on a single report page

C. To filter data only for one visual on the page

D. To filter data across all report pages

Solution

1. Step 1: Understand the scope of page-level filters

   Page-level filters apply to all visuals on the current report page only, not the entire report.

2. Step 2: Compare with other filter types

   Report-level filters affect all pages, visual-level filters affect one visual, and page-level filters affect all visuals on one page.

3. Final Answer:

   To filter data for all visuals on a single report page -> Option B

4. Quick Check:

   Page-level filter = single page visuals filtered [OK]

Hint: Page-level filters affect all visuals on one page only [OK]

Common Mistakes:

• Confusing page-level with report-level filters
• Thinking page-level filters affect only one visual
• Assuming page-level filters change data model

2. Which of the following is the correct way to set a page-level filter in Power BI?

easy

A. Drag a field into the Filters pane under 'Page level filters' and select filter values

B. Write a DAX formula in the data model to filter the page

C. Apply a slicer visual and link it to all pages

D. Change the data source query to filter data

Solution

1. Step 1: Locate the Filters pane in Power BI

   The Filters pane has sections for visual, page, and report level filters.

2. Step 2: Set page-level filter by dragging field

   Drag the desired field into the 'Page level filters' area and choose filter values to apply to all visuals on that page.

3. Final Answer:

   Drag a field into the Filters pane under 'Page level filters' and select filter values -> Option A

4. Quick Check:

   Drag field to page filters area = correct method [OK]

Hint: Drag fields to page filter area to set filters quickly [OK]

Common Mistakes:

• Trying to filter page using DAX formulas
• Using slicers without linking to page filters
• Changing data source instead of filtering in Power BI

3. Consider a report page with a page-level filter set to show only sales from the year 2023. If a visual on this page shows total sales, what will the visual display?

medium

A. Total sales only for the year 2023

B. Total sales for all years in the dataset

C. Total sales for the year 2022

D. No sales data because page-level filters do not affect visuals

Solution

1. Step 1: Understand the effect of page-level filters on visuals

   Page-level filters restrict data for all visuals on that page to the selected filter values.

2. Step 2: Apply the filter context to the visual

   The visual showing total sales will only sum sales data where the year equals 2023.

3. Final Answer:

   Total sales only for the year 2023 -> Option A

4. Quick Check:

   Page filter limits data shown in visuals [OK]

Hint: Page filters limit all visuals to filtered data [OK]

Common Mistakes:

• Ignoring page-level filters effect on visuals
• Assuming visuals show all data ignoring filters
• Confusing page-level with report-level filters

4. You set a page-level filter on the 'Region' field to only show 'West' region data, but some visuals still show data from other regions. What is the most likely cause?

medium

A. The data model has no relationship between Region and sales data

B. The page-level filter was set incorrectly or not applied

C. Page-level filters do not affect visuals at all

D. The visuals have their own visual-level filters overriding the page filter

Solution

1. Step 1: Check filter application and visual filters

   If page-level filter is set correctly, it should affect all visuals unless overridden by visual filters.

2. Step 2: Analyze the scenario

   Since only some visuals show data from other regions, those visuals likely have visual-level filters overriding the page-level filter.

3. Step 3: Identify the root cause

   Visual-level filters override page-level filters, explaining why only some visuals are unaffected.

4. Final Answer:

   The visuals have their own visual-level filters overriding the page filter -> Option D

5. Quick Check:

   Visual-level filters override page-level for specific visuals [OK]

Hint: Check visual-level filters overriding page filters when some visuals ignore them [OK]

Common Mistakes:

• Assuming page filters never apply
• Ignoring data model relationships
• Not checking visual-level filters

5. You want to create a report page that shows sales data filtered by 'Product Category' and 'Year' using page-level filters. However, you also want a slicer on the page to let users filter by 'Region' dynamically. How should you set this up?

hard

A. Use only slicers for all three fields to allow dynamic filtering

B. Set all three fields as page-level filters to restrict data upfront

C. Set 'Product Category' and 'Year' as page-level filters; add a slicer visual for 'Region' on the page

D. Set 'Region' as a page-level filter and 'Product Category' and 'Year' as slicers

Solution

1. Step 1: Apply fixed filters using page-level filters

   Set 'Product Category' and 'Year' as page-level filters to restrict data for all visuals on the page.

2. Step 2: Add dynamic filtering with slicer

   Add a slicer visual for 'Region' so users can interactively filter data by region on the same page.

3. Step 3: Confirm filter behavior

   Page-level filters apply fixed restrictions; slicers allow user-driven filtering dynamically.

4. Final Answer:

   Set 'Product Category' and 'Year' as page-level filters; add a slicer visual for 'Region' on the page -> Option C

5. Quick Check:

   Page filters fixed, slicers dynamic [OK]

Hint: Use page filters for fixed, slicers for dynamic filtering [OK]

Common Mistakes:

• Setting all filters as page-level removes slicer flexibility
• Using only slicers loses fixed filter control
• Confusing filter scopes and user interaction