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.

Practice

1. What does the Single relationship direction mean in Power BI?

easy

A. Filters flow between tables in both directions.

B. Filters flow randomly between tables.

C. Filters do not flow between tables at all.

D. Filters flow from one table to another in only one direction.

Solution

1. Step 1: Understand relationship direction

   Single direction means filter context flows from one table to another, but not back.

2. Step 2: Compare with both directions

   Both directions allow filters to flow both ways, unlike single direction.

3. Final Answer:

   Filters flow from one table to another in only one direction. -> Option D

4. Quick Check:

   Single direction = one-way filter flow [OK]

Hint: Single direction means filter flows one way only [OK]

Common Mistakes:

• Confusing single with both directions
• Thinking filters flow both ways in single direction
• Assuming no filters flow in single direction

2. Which of the following is the correct DAX syntax using CROSSFILTER to enable Both direction filtering in Power BI?

easy

A. CROSSFILTER(Table1[ID], Table2[ID], Both)

B. CROSSFILTER(Table1[ID], Table2[ID], DIRECTION Both)

C. CROSSFILTER(Table1[ID], Table2[ID], BothDirection)

D. CROSSFILTER(Table1[ID], Table2[ID], BothDirections)

Solution

1. Step 1: Recall correct DAX syntax for relationship direction

   Power BI uses CROSSFILTER keyword with values Single or Both to set filter direction.

2. Step 2: Identify correct option

   CROSSFILTER(Table1[ID], Table2[ID], Both) uses CROSSFILTER Both which is the correct syntax for both directions.

3. Final Answer:

   CROSSFILTER(Table1[ID], Table2[ID], Both) -> Option A

4. Quick Check:

   Relationship direction syntax uses CROSSFILTER Both [OK]

Hint: Use CROSSFILTER Both to set both directions in DAX [OK]

Common Mistakes:

• Using DIRECTION keyword instead of CROSSFILTER
• Misspelling Both as BothDirection or BothDirections
• Confusing syntax with SQL JOIN

3. Given two tables, Customers and Orders, with a single direction relationship from Customers to Orders, what happens when you filter Orders by a specific Product?

medium

A. The Customers table is filtered to show only customers who ordered that product.

B. The Orders table is filtered, but Customers table remains unfiltered.

C. Both Customers and Orders tables are filtered by the product.

D. No filtering happens on either table.

Solution

1. Step 1: Understand filter flow in single direction relationship

   Filter flows from Customers to Orders, but not from Orders back to Customers.

2. Step 2: Apply filter on Orders table

   Filtering Orders by product affects Orders only; Customers table remains unfiltered.

3. Final Answer:

   The Orders table is filtered, but Customers table remains unfiltered. -> Option B

4. Quick Check:

   Single direction filters one way only [OK]

Hint: Filter flows only from one side in single direction [OK]

Common Mistakes:

• Assuming filter flows both ways
• Thinking Customers table filters Orders only
• Confusing filter direction with cross-filtering

4. You created a relationship between Sales and Products tables with Both directions, but filtering Products does not filter Sales. What is the likely issue?

medium

A. The relationship is inactive and needs to be activated.

B. The relationship direction is set to Single, not Both.

C. The tables have no matching keys for the relationship.

D. Cross-filtering is disabled in report settings.

Solution

1. Step 1: Check relationship activation status

   Inactive relationships do not apply filters even if direction is Both.

2. Step 2: Confirm relationship direction and keys

   Direction Both is set, and keys exist, so inactive status is the issue.

3. Final Answer:

   The relationship is inactive and needs to be activated. -> Option A

4. Quick Check:

   Inactive relationship blocks filter flow [OK]

Hint: Activate relationships to enable filter flow [OK]

Common Mistakes:

• Assuming direction is wrong without checking activation
• Ignoring inactive relationships
• Thinking cross-filtering is a global setting

5. You have three tables: Customers, Orders, and Products. Customers to Orders has a single direction relationship (Customers filters Orders). Orders to Products has a single direction relationship (Products filters Orders). You want to create a report where filtering Customers also filters Products. What is the best approach?

hard

A. Change both relationships to Both directions to enable filter flow from Customers to Products.

B. Create a direct relationship from Customers to Products with Both directions.

C. Use DAX measures with CROSSFILTER to simulate filter flow from Customers to Products.

D. Keep relationships as is; filtering Customers will automatically filter Products.

Solution

1. Step 1: Analyze existing relationships and filter flow

   Customers filters Orders, Products filters Orders, but no direct filter flow from Customers to Products.

2. Step 2: Understand why filtering Customers does not filter Products

   Because Orders to Products filters from Products to Orders, filter does not flow from Orders to Products.

3. Step 3: Use DAX with CROSSFILTER to simulate filter flow

   Using CROSSFILTER in measures can override filter direction temporarily to enable filtering Products by Customers.

4. Final Answer:

   Use DAX measures with CROSSFILTER to simulate filter flow from Customers to Products. -> Option C

5. Quick Check:

   Use CROSSFILTER in DAX for complex filter flow [OK]

Hint: Use CROSSFILTER in DAX to override filter direction [OK]

Common Mistakes:

• Changing all relationships to Both can cause ambiguity
• Creating unnecessary direct relationships
• Assuming filter flows automatically through multiple single directions