8. Lighthouse

Level 6 · Take the traps out of the relationships

From Chris Morgan, Merchandising Lead

“Someone 'fixed' the category targets model by setting everything to many-to-many and Both. Now the page is slow and the target numbers look odd when I pick a product. I've deleted those two relationships. Can you draw them again properly?”

Your goal

The diagram has order_items, orders, products and Category Targets (one row per category). order_items → orders is already drawn. Draw the two missing relationships the way a star schema wants them: order_items to products, and products to Category Targets on category. Pick the right cardinality, a single direction, active.

  • Every relationship the report needs, set up right

Your model

Model view

The model's tables: order_items (order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total), orders (order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code), products (product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active), Category Targets (category, target_2025).

Already related: order_items[order_id] > orders[order_id] | many-to-one | single | active

Loading the editor…

    ResultPress Check to compare your model

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    Relationships are where a model is fast or slow, clear or ambiguous. Two settings cause most of the trouble: many-to-many cardinality and the Both cross-filter direction.

    What you are working in

    This page is a practice model diagram, not Power BI. You add relationships with the form under the canvas (or as text lines), and the checker compares your diagram with the expected one, relationship by relationship. Like Power BI, it refuses a “one” side whose values repeat.

    In Power BI Desktop: Model view, drag a column onto the matching column, then double-click the line (or Modeling → Manage relationships → New) and set Cardinality and Cross filter direction. Single is Single; two-way is Both.

    Many-to-one, not many-to-many

    Category Targets has six rows, one per category: its category column is unique. Many products share a category. That is exactly many-to-one: products (many) to Category Targets (one).

    Many-to-many is for when neither side is unique. Power BI then can’t use a simple lookup from one row to many; it has to work out which values match on both sides for every query. Totals can look wrong too: a value on the “many” side that matches several rows on the other can be counted in each, and the grand total is not the sum of the rows. When one side is unique, choose many-to-one. When neither is, a small bridge table with one row per value usually turns the many-to-many into two many-to-one relationships.

    Single, not Both

    With Single, filters flow from the one side to the many side: pick a category on the targets table, products filter, order lines filter. That is all a star schema needs.

    With Both, filters also flow backwards, from facts to dimensions. It looks convenient, and it causes three problems:

    • Speed. Each filter now spreads in more directions, so the engine does more work for every visual, and the model gets slower as tables grow.
    • Surprising numbers. Pick one product and the targets table is filtered down to its category, so a “total target” card suddenly shows one category’s target.
    • Ambiguity. With several two-way relationships there can be two paths between the same tables. Power BI refuses to activate a second path, and models end up with inactive relationships nobody understands.

    When one measure really needs a filter to travel upstream, keep the relationship Single and turn on two-way filtering for that calculation only:

    Customers Who Bought = CALCULATE(DISTINCTCOUNT(customers[customer_id]),
    CROSSFILTER(orders[customer_id], customers[customer_id], Both))

    Common mistakes

    • Many-to-many because a column has repeats on one side. Check the other side: if it is unique, it is many-to-one.
    • Both everywhere after a slicer seemed not to work. Usually a relationship was missing.
    • Relating products to targets by name. Relate on the column the targets are defined by: category.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.