Webbo3 Power BI · Cohort 1 · Day 9 of 16 · Lab

Day 9 Lab: Relationships in Model View and First Measures

The Webbo Oil model wired up by the whole class, and a first page of measures everyone can check.

By the end of today you can

Build and inspect every relationship in Model view
Recognise a wrong relationship from what the visuals show
Write the first page of Webbo Oil measures and check them against the class
Use a measure with any dimension: year, station, product, attendant
Start Assignment 2 with a model that works

Two days of theory, now one working model. Same lab rules: every number on the checkpoint list must match before the class moves on.

1. Wiring the Model Together

Definition

Model view shows every table as a card. A line between two cards is a relationship; the 1 and * at its ends show which side is one and which is many; the small arrow in the middle shows which way filters travel.

Real-Life Example

Plumbing. Every pipe must connect the right two points, and the water must flow the right way. A pipe to the wrong tap is worse than no pipe: it looks connected.

The class build

1.Model view › Arrange: Sales in the middle, dimensions around it

2.Home › Manage relationships › Check the four rows below › Close

3.Select each relationship line › Properties pane: Cardinality, Cross filter direction

What we are checkingWe want the full relationship list for the Webbo Oil model, as it should appear in Manage relationships at the end of the lab.

Manage relationships

From: Sales column	To: table[column]	Cardinality	Cross filter
Date	Date[Date]	Many to one	Single
ProductName	Product_Master[ProductName]	Many to one	Single
AttendantName	Attendant_Directory[AttendantName]	Many to one	Single
StationName	Station_Master[StationName]	Many to one	Single

Wrong Relationships, and What They Look Like

Each mistake below was made by somebody in a real class. Learn the symptom; the cause follows.

What this visual showsWe want to see the visual you get if Sales[StationName] is related to Station_Master[StationID] by mistake: no station name ever equals an ID like STN01, so no sale finds its station.

Symptom: everything under (Blank)

Station_Master[Region]	Total Revenue
(Blank)	₦145,854,226
Total	₦145,854,226

What this visual showsWe want to see the visual you get if the Date relationship was never drawn: the year cannot filter Sales, so both years repeat the grand total.

Symptom: the same total on every row

Date[Year]	Total Revenue
2023	₦145,854,226
2024	₦145,854,226
Total	₦145,854,226

Symptom	Cause	Fix
One (Blank) row holding the whole total	Relationship on columns whose values never match	Delete it; relate Sales[StationName] to Station_Master[StationName]
The same number on every row	No relationship between the two tables	Draw it
A dotted line	The relationship is inactive, usually because a second path exists	Remove the extra path (for example Attendant_Directory to Station_Master)
A small (Blank) row next to the real ones	Some keys in Sales match nothing	Left Anti merge in Power Query to find them (Day 5)

Picture This

Picture the plumber checking each pipe runs to the right tap, before turning the water on.

2. The First Page of Measures

Definition

A measure page is a report page of cards and tables whose only job is to check the measures are right before anything pretty is built. Analysts build one in every project.

Real-Life Example

A chef tastes every sauce before plating. The tasting spoon is not for the customer, and skipping it is how a salty dish reaches the table.

Measures from Day 8, typed again from memory

1._Measures › New measure › Total Revenue = SUM ( Sales[TotalAmount_NGN] )

2.New measure › Transactions = COUNTROWS ( Sales )

3.New measure › Average Sale = DIVIDE ( [Total Revenue], [Transactions] )

4.New measure › Attendants Selling = DISTINCTCOUNT ( Sales[AttendantName] )

What this code doesWe want four cards across the top of the measure page, with no filters, that every laptop in the class must match.

Four measures

Total Revenue = SUM ( Sales[TotalAmount_NGN] )
Transactions = COUNTROWS ( Sales )
Average Sale = DIVIDE ( [Total Revenue], [Transactions] )
Attendants Selling = DISTINCTCOUNT ( Sales[AttendantName] )

Measure page: cards

₦145.9M

Total Revenue

6,181

Transactions

₦23,597

Average Sale

Attendants Selling

What we are checkingWe want the measures split by Date[Year], which only works if the Date relationship is right.

Checkpoint: by year

Year	Total Revenue	Transactions	Average Sale
2023	₦57,056,529	3,116	₦18,311
2024	₦88,797,696	3,065	₦28,972
Total	₦145,854,226	6,181	₦23,597

Revenue grew about 56% from 2023 to 2024 while the number of sales barely moved. The growth is price: petrol cost about ₦185 a litre in early 2023 and about ₦900 by late 2024. A finding like that is why you split every measure by year before you trust it.

What we are checkingWe want the measures split by station, checked against the class.

Checkpoint: by station

Station	Region	Total Revenue	Transactions	Average Sale
GRA Filling Station	South South	₦21,582,980	871	₦24,780
Bodija Filling Station	South West	₦21,391,823	884	₦24,199
Independence Layout Filling Station	South East	₦21,222,445	869	₦24,422
Wuse Filling Station	North Central	₦20,591,110	896	₦22,981
Ikeja Filling Station	South West	₦20,455,308	901	₦22,703
Sabon Gari Filling Station	North West	₦20,363,218	892	₦22,829
Lekki Filling Station	South West	₦20,247,342	868	₦23,326
Total		₦145,854,226	6,181	₦23,597

What we are checkingWe want the five attendants with the highest revenue, using the Attendant_Directory dimension through its relationship.

Checkpoint: top 5 attendants

Attendant	Station	Total Revenue
Ifeanyi Suleiman	Sabon Gari Filling Station	₦9,775,323
Chinwe Chukwu	Independence Layout Filling Station	₦9,617,371
Bisi Eze	Lekki Filling Station	₦9,475,602
Chidi Ogunleye	Ikeja Filling Station	₦9,145,573
Ngozi Ogunleye	Independence Layout Filling Station	₦9,086,163

To show only the top five in Power BI: select the table visual, Filters pane, AttendantName, Filter type: Top N, Show items: Top 5, By value: Total Revenue, Apply filter. Then untick (Blank) in the same filter card: those are the unmanned sales, and no attendant earned them.

Try This

Add Product_Master[Category] as a slicer on the measure page and click Fuel. Every card and table should change together. If one does not, its relationship is missing.

Picture This

Picture the chef's tasting spoon: every sauce checked before it goes near a plate.

3. Assignment 2 Is Released

Assignment 2 asks you to build this whole model yourself, from the raw files, and to explain it. It is due on Day 11.

Read the Brief Tonight

The full brief is on the Assignment 2 page, the next lesson in this course.

Do This Now

Check your four relationships in Manage relationships.
Break one on purpose (relate Station to StationID), look at the symptom, then fix it.
Build the measure page: four cards, the by-year table, the by-station table.
Add the Top 5 attendants table with a Top N filter.
Check every number against this lesson, and read the Assignment 2 brief.

Checkpoint

Q1. A visual shows one (Blank) row with the whole total. What happened?

The relationship joins columns whose values never match, so no sale finds its dimension row.

Q2. Both years show the same revenue. What is missing?

The relationship from Date[Date] to Sales[Date].

Q3. Why build a measure page before a dashboard?

To prove each measure is right under every filter while it is easy to check, before design hides the numbers.

Q4. Why did revenue grow so much more than the number of sales?

Prices rose sharply over the two years, especially petrol. Revenue grew mostly from price, not from more customers.

Tick Before You Move On

☐ Four relationships, all Many to one and Single
☐ Every card on my measure page matches the lesson
☐ The by-year and by-station tables split and add up
☐ I can name the symptom of a wrong and of a missing relationship
☐ I have read the Assignment 2 brief

Quick Recap

Model view lines are relationships; 1 and * show the sides; the arrow shows the filter direction. A (Blank) row holding everything means keys that never match. The same number everywhere means no relationship. A measure page checks the numbers before design begins. Split every measure by year before trusting it.

Final Scenario

Your measure page says revenue grew sharply, and the manager is delighted.

You show her the transactions column next to it: sales grew much less. You explain the price rise, and she changes her plan from "hire more attendants" to "watch pump prices". The measure page just paid for itself.

Numbers you have checked are numbers you can argue with.

Next: the Assignment 2 brief, then Day 10, CALCULATE.

Day 9 Lab: Relationships in Model View and First Measures

1. Wiring the Model Together

Definition

Real-Life Example

Wrong Relationships, and What They Look Like

2. The First Page of Measures

Definition

Real-Life Example

3. Assignment 2 Is Released

Do This Now

Checkpoint

Learn this with a real instructor