Relationship direction and cross-filtering in Power BI - Cell-by-Cell Formula Trace

Sample Data

Two tables: Products (A1:B4) and Orders (D1:F4). Products has ProductID and ProductName. Orders has OrderID, ProductID (foreign key), and Quantity.

Cell	Value
A1	ProductID
B1	ProductName
A2	101
B2	Chair
A3	102
B3	Table
A4	103
B4	Lamp
D1	OrderID
E1	ProductID
F1	Quantity
D2	5001
E2	101
F2	2
D3	5002
E3	103
F3	1
D4	5003
E4	102
F4	4

Formula Trace

CALCULATE(SUM(Orders[Quantity]), Products[ProductName] = "Chair")

Step 1: Products[ProductName] = "Chair"

Step 2: Cross-filter from Products to Orders via ProductID relationship

Step 3: SUM(Orders[Quantity]) on filtered Orders

Step 4: CALCULATE result

Cell Reference Map

Products Table       Orders Table
+----+------------+   +-------+----------+----------+
| A1 | B1         |   | D1    | E1       | F1       |
|----|------------|   |-------|----------|----------|
|101 | Chair      |   |5001   | 101      | 2        |
|102 | Table      |   |5002   | 103      | 1        |
|103 | Lamp       |   |5003   | 102      | 4        |
+----+------------+   +-------+----------+----------+

Relationship: Products[ProductID] --> Orders[ProductID]
Filter direction: Single from Products to Orders

The formula filters Products by ProductName 'Chair' then filters Orders by matching ProductID via the relationship. The filter flows from Products to Orders.

Result

+-----------------------------+
| CALCULATE Result             |
|-----------------------------|
| 2                           |
+-----------------------------+

The final result shows 2, which is the total quantity of orders for the product 'Chair'. This is because only one order has ProductID 101 (Chair) with quantity 2.

Sheet Trace Quiz - 3 Questions

What does the filter 'Products[ProductName] = "Chair"' do in the formula?

A Filters Orders table directly for 'Chair'

B Keeps only products named 'Chair' in the Products table

C Removes all products except 'Table'

D Filters Orders for quantity greater than 2

Key Result

CALCULATE with filter on one table applies cross-filtering via relationship direction to related tables.