Cross-filtering between visuals in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants an interactive sales report where selecting a region filters the sales data by product category and vice versa.

📊 Data: You have sales data with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a Power BI report with two visuals: a bar chart for Sales by Region and a pie chart for Sales by Product Category. Enable cross-filtering so selecting a region updates the product category sales and selecting a product category updates the region sales.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-10	South	Clothing	800
2024-01-15	East	Electronics	950
2024-01-20	West	Clothing	700
2024-01-25	North	Clothing	650
2024-02-01	South	Electronics	1100
2024-02-05	East	Clothing	500
2024-02-10	West	Electronics	1300
2024-02-15	North	Electronics	1400
2024-02-20	South	Clothing	900

1

Step 1: Load the sales data into Power BI Desktop.

Use 'Get Data' > 'Excel/CSV' or paste data directly.

Expected Result

Sales data table appears in Power BI with columns Date, Region, Product Category, Sales Amount.

2

Step 2: Create a bar chart visual showing total sales by Region.

Drag 'Region' to Axis and 'Sales Amount' to Values (aggregation: Sum).

Expected Result

Bar chart displays total sales for each region: North, South, East, West.

3

Step 3: Create a pie chart visual showing total sales by Product Category.

Drag 'Product Category' to Legend and 'Sales Amount' to Values (aggregation: Sum).

Expected Result

Pie chart displays sales distribution between Electronics and Clothing.

4

Step 4: Enable cross-filtering between the two visuals.

Select the bar chart, then in Format pane > Edit interactions, ensure the pie chart is set to filter when a region is selected. Repeat for pie chart to filter bar chart by product category.

Expected Result

Selecting a region in the bar chart filters the pie chart to show sales only for that region's product categories, and selecting a product category in the pie chart filters the bar chart to show sales only for regions selling that category.

5

Step 5: Test the cross-filtering by selecting 'North' region in the bar chart.

Click on the 'North' bar in the bar chart.

Expected Result

Pie chart updates to show sales for Electronics and Clothing only in the North region.

6

Step 6: Test the cross-filtering by selecting 'Clothing' in the pie chart.

Click on the 'Clothing' slice in the pie chart.

Expected Result

Bar chart updates to show sales for Clothing only across all regions.

Final Result

Bar Chart: Sales by Region

↓

Pie Chart: Sales by Product Category

✓North region has the highest total sales.

✓Electronics sales are almost double Clothing sales overall.

✓Cross-filtering helps quickly see which product categories perform best in each region.

Bonus Challenge

Add a slicer for Date to filter sales by month and observe how cross-filtering updates accordingly.

Show Hint

Use the Date column to create a slicer visual. Selecting a month should filter both bar and pie charts dynamically.

Practice

1. What does cross-filtering between visuals in Power BI allow you to do?

easy

A. Export data from all visuals at once

B. Change the color scheme of all visuals simultaneously

C. Click one visual to filter data in other visuals on the report page

D. Automatically refresh data sources without user action

Solution

1. Step 1: Understand cross-filtering purpose

   Cross-filtering links visuals so selecting data in one affects others.

2. Step 2: Identify correct behavior in Power BI

   Clicking a visual filters related data in other visuals on the same report page.

3. Final Answer:

   Click one visual to filter data in other visuals on the report page -> Option C

4. Quick Check:

   Cross-filtering = interactive filtering [OK]

Hint: Cross-filtering links visuals by selection to filter others [OK]

Common Mistakes:

• Thinking cross-filtering changes colors
• Confusing cross-filtering with data export
• Assuming it refreshes data automatically

2. Which setting in Power BI controls how one visual filters another?

easy

A. Data source credentials

B. Cross-filtering options in the Format pane

C. Page size settings

D. Visual title text

Solution

1. Step 1: Locate cross-filtering controls

   Power BI allows customizing cross-filtering behavior in the Format pane of visuals.

2. Step 2: Identify correct setting

   The Format pane includes options to set cross-filtering or cross-highlighting between visuals.

3. Final Answer:

   Cross-filtering options in the Format pane -> Option B

4. Quick Check:

   Cross-filtering setting = Format pane option [OK]

Hint: Cross-filtering settings are in Format pane, not data or page settings [OK]

Common Mistakes:

• Confusing cross-filtering with data source settings
• Looking in page size or title settings
• Assuming cross-filtering is automatic without configuration

3. Given two visuals: a bar chart showing sales by region and a table showing customer details, what happens if you click a bar for 'West' region when cross-filtering is enabled?

medium

A. The table updates to show only customers from the West region

B. The bar chart changes to show only customers from the West region

C. Nothing changes in either visual

D. The table shows all customers regardless of region

Solution

1. Step 1: Understand cross-filtering effect on visuals

   Clicking a bar filters related visuals to show data matching that selection.

2. Step 2: Apply to given visuals

   Clicking 'West' bar filters the customer table to only show customers from West region.

3. Final Answer:

   The table updates to show only customers from the West region -> Option A

4. Quick Check:

   Click bar filters table data [OK]

Hint: Clicking a visual filters related visuals to matching data [OK]

Common Mistakes:

• Thinking the bar chart itself changes
• Assuming no change happens
• Believing the table shows all customers always

4. You set cross-filtering between two visuals, but clicking one visual does not filter the other. What is the most likely cause?

medium

A. The data model has no relationship between the tables used

B. Cross-filtering is disabled or set to 'None' in Format pane

C. The visuals are on different report pages

D. The report is in Reading view instead of Editing view

Solution

1. Step 1: Check data model relationships

   Cross-filtering requires a relationship between tables used in visuals to filter correctly.

2. Step 2: Identify cause of no filtering

   If no relationship exists, visuals cannot filter each other despite cross-filtering settings.

3. Final Answer:

   The data model has no relationship between the tables used -> Option A

4. Quick Check:

   Missing relationship blocks cross-filtering [OK]

Hint: Check data relationships if cross-filtering fails [OK]

Common Mistakes:

• Assuming visuals on different pages can cross-filter
• Ignoring data model relationships
• Thinking view mode blocks filtering

5. You have a report with a slicer visual filtering product categories and a bar chart showing sales by region. You want selecting a region in the bar chart to also filter the slicer to only show categories sold in that region. How do you configure this cross-filtering behavior?

hard

A. Disable cross-filtering in the Format pane for both visuals

B. Set the slicer visual to ignore cross-filtering from the bar chart

C. Place both visuals on separate report pages

D. Enable bi-directional cross-filtering between the product and region tables in the data model

Solution

1. Step 1: Understand cross-filtering direction

   By default, filtering flows one way; to filter slicer from bar chart, bi-directional filtering is needed.

2. Step 2: Configure data model relationships

   Enable bi-directional cross-filtering between product and region tables to allow filtering both ways.

3. Final Answer:

   Enable bi-directional cross-filtering between the product and region tables in the data model -> Option D

4. Quick Check:

   Bi-directional filtering enables slicer update from bar chart [OK]

Hint: Use bi-directional filtering to sync slicer and chart filters [OK]

Common Mistakes:

• Ignoring filtering direction in relationships
• Putting visuals on different pages
• Disabling cross-filtering accidentally