Level 4 · Relate order lines, orders and customers

Jason’s state slicer filters customers, but units live in order_items, two tables away. A filter can cross more than one relationship, as long as each one points the right way.

What you are working in

This is a practice diagram built for this site, because Power BI Desktop runs only on Windows. You add relationships with the form under the canvas; each one is checked against the data the way Power BI checks it, and then compared with the model the report needs.

In Power BI Desktop: Model view → drag order_id from order_items onto order_id in orders, then customer_id from orders onto customer_id in customers. Or Modeling → Manage relationships → New, pick the two tables and columns, Cardinality: Many to one (*:1), Cross filter direction: Single, and tick Make this relationship active.

Find the path

Write down what each table knows:

Table	Key (unique)	Points at
order_items	order_item_id	order_id, product_id
orders	order_id	customer_id
customers	customer_id

order_items can’t reach customers directly: it has no customer_id. It knows its order, and the order knows its customer. So the model needs two relationships, each from a foreign key to the key it points at:

order_items[order_id]   > orders[order_id]
orders[customer_id]     > customers[customer_id]

How the filter travels

Pick “CA” on the state slicer:

1. customers keeps only Californian customers.
2. Through orders[customer_id] → customers[customer_id], orders keeps only their orders.
3. Through order_items[order_id] → orders[order_id], order_items keeps only those orders’ lines.

SUM(order_items[quantity]) now adds up Californian units. Each hop goes from a “one” side to a “many” side, which is the direction a Single relationship passes filters. If either relationship were missing or pointed the other way, the chain would break and every state would show the same grand total.

Many-to-one, twice

Both relationships are many-to-one: many lines per order, many orders per customer. The “one” side is always a key, unique in its table. Power BI checks this against the data; if you accidentally put orders on the one side of the customer_id relationship, it refuses, because orders[customer_id] repeats.

Common mistakes

• Skipping the middle table. Lines and customers share no column. The path goes through orders.
• Reversed sides. customers[customer_id] > orders[customer_id] | many-to-one claims each order has many customers. Power BI refuses it: the one side has repeated values.
• Both directions. A filter on order_items (by product, say) doesn’t need to reach customers for this report. Keep Single.