The request
From Heather Lewis, Head of Data
“We're standardising on the star. Here are the five tables for the company sales model, freshly loaded and with no relationships. Build it the way we agreed: facts in the middle, every dimension one hop away, single direction, the calendar marked, and the signup date ready for the marketing team without breaking sales.”
Your goal
Step 1 of 3. Relate the order lines. Connect order_items to orders and order_items to products, many-to-one, single direction, active.
- Every relationship the report needs, set up rightnot done yet
Step 2 of 3. Keep your two relationships and add the ones that hang off orders: orders to customers on the customer, orders to 'Date' on the order date (many-to-one, single, active). Then mark 'Date' as the date table.
- Every relationship the report needs, set up rightnot done yet
- The date table markednot done yet
Step 3 of 3. Marketing wants signups by month from the same calendar. Add customers[signup_date] to 'Date'[Date], many-to-one, single direction, without changing any path the sales measures use.
- Every relationship the report needs, set up rightnot done yet
- The date table markednot done yet
Step 1 · your model
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), customers (customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source), Date (Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday).
Loading the editor…
Reference solution
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 2 · your model
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), customers (customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source), Date (Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday).
Loading the editor…
Reference solution
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 3 · your model
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), customers (customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source), Date (Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday).
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 Heather gets, and why
The lesson
This is the model every later chapter uses. You build it from nothing: five tables, five relationships, one date-table mark.
What you are working in
A practice diagram built for this site, because Power BI Desktop runs only on Windows. Each step adds to the previous one, and the diagram checks the data the way Power BI does before comparing your model with the target. In Power BI Desktop the same work happens in Model view, with Modeling → Manage relationships for anything you want to set precisely, and Table tools → Mark as date table for the calendar.
Start with the grain of each table
| Table | One row is one… | Key | Points at |
|---|---|---|---|
| order_items | order line | order_item_id | order_id, product_id |
| orders | order | order_id | customer_id, order_date |
| products | product | product_id | |
| customers | customer | customer_id | signup_date |
| Date | day | Date |
Every arrow in the last column becomes a relationship from that column to the key it names. Read the table once and the whole model is in front of you.
The order of work
- The facts first.
order_itemspoints atorders(its header) and atproducts. These two relationships make the line table the centre of the star. - What hangs off the order. Customers and dates belong to the order, not the line: one
order has one customer and one date, whatever it contains. So
ordersrelates tocustomersand to'Date', and the calendar is marked. - Extra roles. The signup date is a second date on
customers. Since'Date'already reachescustomersthroughorders, the new relationship must be inactive.
Check it like a reviewer
Once it’s built, pick any dimension and trace a filter to order_items:
- a category: products → order_items (one hop);
- a state: customers → orders → order_items (two hops);
- a month: Date → orders → order_items (two hops).
Each path is unique and runs from a “one” side to a “many” side. That is what makes it a star:
any measure on order_items responds to every slicer without a single line of special DAX.
Common mistakes
- Products related to orders. Orders don’t have a product; lines do.
- Date related to order lines. Lines have no date. The order date lives on
orders. - Both directions to “make the slicers talk”. It creates ambiguous paths in exactly this kind of model. Keep Single.
- A second active date relationship. Only one active path; the rest wait for USERELATIONSHIP.
Lantern lit!
Practised: star schemarelationshipdate tablemark as date tableinactive relationshipdata model
- Solved
- No hints
- First run