Relationships (one-to-many, many-to-many) in Power BI - Cell-by-Cell Formula Trace

Sample Data

Two tables: Customers (A1:B4) and Orders (D1:F5). Customers have unique CustomerID. Orders link to Customers by CustomerID, showing a one-to-many relationship.

Cell	Value
A1	CustomerID
B1	CustomerName
A2	1
B2	Alice
A3	2
B3	Bob
A4	3
B4	Charlie
D1	OrderID
E1	CustomerID
F1	OrderAmount
D2	101
E2	1
F2	250
D3	102
E3	2
F3	450
D4	103
E4	1
F4	300
D5	104
E5	3
F5	150

Formula Trace

SUMX(RELATEDTABLE(Orders), Orders[OrderAmount])

Step 1: RELATEDTABLE(Orders) for CustomerID = 1

Step 2: SUMX(Orders rows for CustomerID 1, Orders[OrderAmount])

Step 3: RELATEDTABLE(Orders) for CustomerID = 2

Step 4: SUMX(Orders rows for CustomerID 2, Orders[OrderAmount])

Step 5: RELATEDTABLE(Orders) for CustomerID = 3

Step 6: SUMX(Orders rows for CustomerID 3, Orders[OrderAmount])

Cell Reference Map

Customers Table       Orders Table
+------------+       +------------+------------+------------+
| A1:CustomerID |       | D1:OrderID | E1:CustomerID | F1:OrderAmount|
| B1:CustomerName |       |            |            |            |
| A2:1       |       | D2:101     | E2:1       | F2:250     |
| B2:Alice   |       | D3:102     | E3:2       | F3:450     |
| A3:2       |       | D4:103     | E4:1       | F4:300     |
| B3:Bob     |       | D5:104     | E5:3       | F5:150     |
| A4:3       |       +------------+------------+------------+
| B4:Charlie |
+------------+

Arrows:
CustomerID in Customers (A2:A4) links to CustomerID in Orders (E2:E5)

The relationship is between Customers[CustomerID] and Orders[CustomerID]. Orders table has many rows for one CustomerID, showing one-to-many.

Result

Customers with Total Order Amount
+------------+---------+-------------------+
| CustomerID | Name    | TotalOrderAmount  |
+------------+---------+-------------------+
| 1          | Alice   | 550               |
| 2          | Bob     | 450               |
| 3          | Charlie | 150               |
+------------+---------+-------------------+

For each customer, the total order amount is calculated by summing all their related orders. This shows the one-to-many relationship in action.

Sheet Trace Quiz - 3 Questions

What does RELATEDTABLE(Orders) return for CustomerID 1?

A Only the first order in Orders

B All customers with CustomerID 1

C All orders with CustomerID 1

D Sum of all order amounts

Key Result

RELATEDTABLE returns all rows from the related table matching the current row's key, enabling aggregation over one-to-many relationships.