Relationship direction and cross-filtering in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand how product categories influence sales by region. They ask you to build a report that shows total sales by product category and region, ensuring that filtering on either category or region updates the other correctly.

📊 Data: You have two tables: 'Sales' with columns OrderID, ProductID, Region, and SalesAmount; and 'Products' with columns ProductID and Category. The tables are related by ProductID.

🎯 Deliverable: Create a Power BI report with a matrix visual showing total sales by Category and Region. Set up the relationship between tables with the correct direction and cross-filtering so that filtering on Category or Region works both ways.

Sample Data

Sales
OrderID	ProductID	Region	SalesAmount
1001	1	North	200
1002	2	South	150
1003	3	East	300
1004	1	West	250
1005	2	North	100
1006	3	South	350

Products
ProductID	Category
1	Electronics
2	Furniture
3	Clothing

1

Step 1: Load the 'Sales' and 'Products' tables into Power BI.

Use 'Get Data' to import both tables from your data source.

Expected Result

Both tables appear in the Power BI Fields pane.

2

Step 2: Create a relationship between 'Products[ProductID]' and 'Sales[ProductID]'.

In the Model view, drag 'ProductID' from 'Products' to 'ProductID' in 'Sales'. Set relationship to 'Many to One' with 'Products' as the one side.

Expected Result

A relationship line appears between the two tables.

3

Step 3: Set the relationship's cross-filter direction to 'Both'.

Double-click the relationship line, set 'Cross filter direction' to 'Both', then click OK.

Expected Result

Filters applied on either table affect the other.

4

Step 4: Create a matrix visual with 'Category' from 'Products' and 'Region' from 'Sales'.

Add a Matrix visual. Drag 'Category' and 'Region' fields into Rows, and add a measure for total sales.

Expected Result

Matrix shows rows grouped by Category and Region.

5

Step 5: Create a measure for total sales.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure calculates total sales correctly.

6

Step 6: Add the 'Total Sales' measure to the matrix visual's values.

Drag 'Total Sales' measure into Values field of the matrix visual.

Expected Result

Matrix shows total sales amounts for each Category and Region combination.

7

Step 7: Test filtering by selecting a Category or Region in slicers or the matrix.

Add slicers for 'Category' and 'Region'. Select a value in one slicer and observe the other updates.

Expected Result

Filtering on Category updates Region filter and vice versa, showing correct sales data.

Final Result

-----------------------------------------
| Category    | Region | Total Sales     |
|-------------|--------|----------------|
| Electronics | North  | 200            |
| Electronics | West   | 250            |
| Furniture   | South  | 150            |
| Furniture   | North  | 100            |
| Clothing    | East   | 300            |
| Clothing    | South  | 350            |
-----------------------------------------

✓Electronics sales are strong in North and West regions.

✓Furniture sales are mainly in South and North regions.

✓Clothing sales lead in East and South regions.

✓Cross-filtering allows exploring sales by category or region interactively.

Bonus Challenge

Add a slicer for 'Region' and a slicer for 'Category' and configure them so selecting a value in one slicer filters the other dynamically.

Show Hint

Ensure the relationship cross-filter direction is set to 'Both' and use slicers from both tables.