Why data modeling connects related tables in Power BI - Formula Trace Breakdown

Sample Data

Two tables: Customers (A1:B3) and Orders (D1:F4). Customers have IDs and names. Orders have IDs, CustomerIDs, and amounts.

Cell	Value
A1	CustomerID
B1	CustomerName
A2	101
B2	Alice
A3	102
B3	Bob
D1	OrderID
E1	CustomerID
F1	OrderAmount
D2	5001
E2	101
F2	250
D3	5002
E3	102
F3	450
D4	5003
E4	101
F4	300

Formula Trace

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

Step 1: RELATEDTABLE(Orders) for CustomerID 101

Step 2: SUMX(Table with OrderAmount 250, 300, expression Orders[OrderAmount])

Cell Reference Map

Customers Table       Orders Table
+----+-------------+   +------+------------+------------+
| A1 | B1          |   | D1   | E1         | F1         |
|----|-------------|   |------+------------+------------|
|101 | Alice       |   |5001  | 101        | 250        |
|102 | Bob         |   |5002  | 102        | 450        |
|    |             |   |5003  | 101        | 300        |
+----+-------------+   +------+------------+------------+

Arrows: CustomerID in Customers connects to CustomerID in Orders.

CustomerID in Customers table links to CustomerID in Orders table to connect related data.

Result

CustomerID | CustomerName | TotalOrderAmount
-------------------------------------------
101        | Alice        | 550
102        | Bob          | 450

Shows total order amount per customer by connecting Customers and Orders tables using CustomerID.

Sheet Trace Quiz - 3 Questions

Why do we connect CustomerID in Customers to CustomerID in Orders?

A To delete duplicate customers

B To combine related data from both tables for analysis

C To sort orders by amount

D To change customer names

Key Result

RELATEDTABLE returns all rows from a related table matching the current row's key, enabling aggregation like SUMX.