The request
From Rachel Kim, Category Manager
“I put a category slicer on the sales page and it does nothing: every category shows the same total. Someone said the tables aren't connected. Can you fix the model so picking Lighting actually shows Lighting?”
Your goal
The model has order_items (one row per order line) and products (one row per product), with no relationship yet. Create the relationship that lets a filter on products reach order_items: the right columns, the right cardinality, a single cross-filter direction, active.
- Every relationship the report needs, set up rightnot done yet
Your model
The model's tables: order_items (order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total), products (product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active).
Loading the editor…
Reference solution
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Rachel gets, and why
The lesson
Two tables in a report are strangers until you relate them. Rachel’s slicer filters products,
but her sales numbers come from order_items, so the filter has nowhere to go and every category
shows the grand total.
Keys: the column both tables share
A relationship matches one column in each table. On one side it is the table’s primary
key, the column with exactly one row per value: products[product_id] names one product. On the
other side it is a foreign key, a column that points at that key and may repeat:
order_items[product_id] appears on every order line that sold the product.
Cardinality: how many rows match
- Many-to-one (*:1): many order lines, one product. The normal case, and the one you want.
- One-to-one (1:1): both sides unique. Rare; often a sign the two tables should be one.
- Many-to-many (*:*): neither side unique. Power BI allows it, but totals get hard to read. Avoid it unless you know why you need it.
Power BI checks the data: if you claim a “one” side whose values repeat, it refuses the relationship. This practice diagram does the same.
Direction: which way filters flow
The cross-filter direction says which table filters which. With Single, the one side
(the dimension, products) filters the many side (the fact, order_items). That is what Rachel
needs: pick Lighting, see Lighting’s order lines. Both also sends filters back from facts to
dimensions; it has uses, but as a default it causes ambiguous paths and slow reports.
In Power BI Desktop
Open Model view (the third icon on the left rail) and drag product_id from order_items
onto product_id in products. Power BI guesses the cardinality and direction; check them by
double-clicking the line, or use Modeling → Manage relationships → New and pick both columns,
Many to one (*:1), Cross filter direction: Single, and Make this relationship active.
In this practice diagram, the form under the canvas does the same.
Common mistakes
- Relating by name. Names repeat and change. Keys don’t.
- Many-to-many by accident. Usually means one side isn’t the key column.
- Both directions “just in case”. Keep Single unless a ticket gives you a reason.
Lantern lit!
Practised: relationshipcardinalitymany to onecross filter directionprimary keyforeign key
- Solved
- No hints
- First run