Business Intelligence with Power BI

L04 · The Data Model

Practice — Medium · Break It, Read It, Fix It

Skills focus. Diagnosing a model from its symptoms — the skill you will actually use, because you will inherit more broken models than you build.

Ungraded. Start from your finished Mild star (or A04). You will deliberately damage it, read the damage from the report side, and repair it. Nothing here is destructive if you follow the steps — but work on a copy of the file if that helps you relax.

Part 1 — The silent card

  1. On your report page (units card, category bar, region slicer), confirm everything moves when you click a region.
  2. Model view: delete the sales → stores relationship.
  3. Report view: click a region in the slicer. Write down exactly which visuals moved and which sat still — and notice that nothing errored, nothing turned red, and the card is showing a confidently wrong "everything" total while the slicer claims to filter.
  4. Recreate the relationship (drag StoreID onto StoreID; confirm *:1, single direction). Verify the card moves again.

Part 2 — The table with no key

  1. Load inventory.csv (5,700 rows: month, store, category, units on hand).
  2. Someone suggests: "relate stores to inventory — StoreID to StoreID." Run the key check on both sides first and predict what the dialog will offer.
  3. Now the harder question: is there any single column in inventory that could serve as a "one" side for some other table? Profile each column and decide what kind of table inventory is.
  4. Create the correct relationship(s) for inventory and add a quick proof visual: units on hand by Region.

Part 3 — The multiplication, witnessed

  1. On a copy of the file: create the forbidden returns→sales relationship on OrderID. Accept the many-to-many warning.
  2. Put QuantityReturned and sales' Quantity in a single table visual grouped by Category, and compare the Quantity column against the star's verified figures (Lighting 60,508 units sold). Note anything that changed, then delete the file copy — the point was to see the machinery misbehave once, on purpose, where it costs nothing.

Reflection

  • Part 1's broken state produced no error — only a card that ignored a slicer. In a 50-visual report, how would you find this fault? What habit makes it findable?
  • Part 2: inventory turned out to be a fact with a compound grain (store × category × month). What does that tell you about the claim "every table needs an ID column"?