Data relationships

01The problem

You put region from the customers table and revenue from orders into a bar chart, and every bar shows ₦830.5 million. Power BI doesn't know which orders belong to which region. The tables need a relationship.

02The concept

A relationship links two tables through a column they share, usually an ID.

• Cardinality. Almost always one-to-many (shown as 1 and *): one customer, many orders. The "one" side must have unique values; customers[customer_id] does.
• Cross-filter direction. The arrow on the line shows which way filters flow. Single (from the one side to the many side) is the default and the right choice almost always: choosing a region filters the customers, which filters their orders.
• Active vs inactive. Only one active path can exist between two tables; others show as dashed lines and are used only when a DAX formula asks for them.

Star schema. The standard design, which the next lesson explores further:

            customers
                │ 1
                │
products ─1───* orders *───1─ Date

• Fact table in the middle: events with numbers (orders).
• Dimension tables around it: the things you filter and group by (customers, products, dates).

Filters flow from dimensions into the fact table. Put fields from dimensions on axes and slicers, and numbers from the fact table in values.

03Example

Here is Kolanut's model in Model view, with the relationships Power BI detected when the tables were loaded:

Read each line: the 1 sits beside the table where each ID appears once (products, customers); the * (many) sits beside orders. The small arrow on the line shows the filter direction: from the one side into orders.

With the relationship customers[customer_id] (1) → orders[customer_id] (*):

Without it, every row shows ₦830.5m.

04Walkthrough

1. Go to Model view. Power BI may already have created relationships: it auto-detects matching column names on load. Check them rather than trusting them.
2. If customers and orders aren't connected, drag customer_id from customers onto customer_id in orders.
3. Double-click the line to open Edit relationship. Check: Cardinality Many to one (*:1) from orders to customers; Cross filter direction Single; Make this relationship active ticked.
4. Do the same for products[product_id] → orders[product_id].
5. If you merged category into orders in lesson 4, delete that column now (in Power Query, delete the merge steps) and use products[category] instead.
6. Back in Report view, build a table visual with customers[region] and orders[revenue]. Each region should show a different number.

05Practice

06Check your understanding