Practice Midterm — Cobble Hill Studios

What this is. A full rehearsal of the midterm practical on a fresh dataset — ungraded, self-timed, complete solution below. The real midterm hands you a company you have never seen, because the skills are supposed to transfer. So does this one: Cobble Hill Studios is a fitness-studio network, not a retailer and not a clinic. If you can do this in 75 minutes without opening the solution, you are ready.

This rehearsal carries the same machine-gradable contract as the real exam — the identical checklist format, checked the identical way. Where automated grading is enabled, submit PRACTICE_LastFirst.pbix and the feedback you get here is the feedback format you will get there.

How to use it honestly

Set a 75-minute timer, put your notes where you would have them in the exam, and do not scroll past the checklist until the timer ends or you finish. The solution at the bottom is a debrief, not a walkthrough to follow along with — reading it first turns a rehearsal into a rerun, and the real exam will feel like the first time you ever did this under a clock. The failure mode this practice exists to catch is not "I don't know CALCULATE"; it is "I know CALCULATE but spent twenty minutes on the roster cleanup." Find that out here.

Scenario

Cobble Hill Studios runs 8 fitness studios across three districts. You receive one year of class-session data (August 2025 – July 2026), a clean studio list, and an instructors export that — like every roster export in history — has problems. Leadership wants a working model and four honest numbers.

Files (in Downloads, under Practice midterm world): sessions.csv · studios.csv · instructors_export.csv

Tasks

Clean the instructors export (instructors_export.csv, 45 rows). Remove exact duplicates and convert the text "N/A" disciplines to true nulls. The clean result has 38 instructors.
Load sessions.csv (9,085 rows) and studios.csv (8 rows). Model: sessions → instructors (InstructorID) and sessions → studios (StudioID), both many-to-one, single direction. Run the key checks first — as on the real exam, one of the three tables cannot be a "one" side until you fix something; know which and why before the dialog tells you.
Measures (exact names):
- Session Count — all sessions
- Cancellation Rate — canceled sessions over all sessions, DIVIDE, percent format
- Completed Revenue — fees for Completed sessions only (a CALCULATE with a status filter)
- Avg Revenue per Completed — composed from measures you already have
One report page: the four measures as cards, plus a bar of Completed Revenue by studio District and a Discipline slicer that moves everything.

Checklist (same shape as the real one)

#	Check
1	Instructors cleaned: 38 rows, disciplines are true nulls (not the text "N/A")
2	Model: two correct relationships, correct direction; no incorrect extras
3	Four measures, exact names, verified values
4	Report page: four cards, district bar, working discipline slicer

Concept warm-up (eight questions, answers in the solution)

A colleague adds Fee to a card and gets a number. You write an explicit SUM measure and get the same number. Name two reasons the explicit measure is still the right call.
The instructors export has duplicate rows. In Lesson 3, six monthly sales files also had duplicate rows — and deleting those would have destroyed real sales. What makes duplicates safe to delete here and dangerous there?
Why can't the uncleaned instructors export be the "one" side of a relationship? What exactly does Power BI check when you try?
Your Cancellation Rate shows 0.062 instead of 6.2%. Is the number wrong, the format wrong, or both? What is the fix?
Completed Revenue uses CALCULATE with a status filter. In one sentence: what does CALCULATE do to the filter context that a plain SUM cannot?
A card shows total revenue. A slicer selection changes it. Where did the filter travel, and through what?
You relate sessions to studios and drag District onto a bar of Completed Revenue. What path does each bar's number take through the model?
The real exam gives you a dataset you have never seen. What is the first thing to do with any new table, before building a single visual — and which view do you do it in?

Solution and debrief — open after your attempt

1 · The roster cleanup

The export holds 45 rows for 38 instructors: seven rows are exact duplicates of rows already present. Remove Duplicates (all columns selected) takes 45 → 38 in one step. These duplicates are safe to delete precisely because they carry no line identity of their own — an instructor listed twice is an export glitch, not two facts. (Contrast Lesson 3: identical sales lines were real repeat purchases, and the file had no line-number column to distinguish them. Same button, opposite verdicts — the difference is what a row means.)

Then Replace Values: "N/A" → null on Discipline. After the dedup, exactly 4 instructors carry a true null discipline. If you see 5, you replaced before deduplicating and one N/A row was itself a duplicate — order matters less than checking the result; either order lands on 4 after both steps.

2 · The model

The un-cleaned export cannot be a "one" side because Power BI validates uniqueness of the key column at relationship time, and InstructorID appears twice for seven IDs. Clean first, relate second: sessions → instructors on InstructorID, sessions → studios on StudioID, both many-to-one, single direction, sessions on the "many" side of both. Nothing relates instructors to studios directly for this exam's questions — an extra relationship there is exactly the kind of "incorrect extra" the checklist means.

3 · The measures, with the numbers you should have

Measure	Expected value	Watch for
`Session Count` = COUNTROWS(sessions)	9,085	Counting a column with blanks instead of rows
`Cancellation Rate` = DIVIDE(canceled, all)	6.20%	0.062 unformatted; or filtering the denominator too
`Completed Revenue` = CALCULATE(SUM(Fee), Status = "Completed")	$239,025.52	$254,625.32 means you summed all sessions, cancellations included
`Avg Revenue per Completed` = DIVIDE([Completed Revenue], CALCULATE([Session Count], sessions[Status] = "Completed"))	$28.05	Dividing by plain [Session Count] (9,085) gives $26.31 — wrong denominator, plausible-looking number

That last row is the exam's sharpest lesson: $26.31 looks fine. Nothing errors. The only defense is composing from the measure that already holds the right filter — which is why the task says "composed from measures you already have."

4 · The page

Four cards, a bar of Completed Revenue by District — Capital leads at $116,712.81, Coastal $80,277.60, Western $42,035.11 — and a Discipline slicer. Click Spin and every number on the page should move at once; a card that sits still is sitting on a broken filter path, and the model view will show you where.

Concept warm-up answers

An explicit measure has a name other measures can build on, and one definition that cannot drift per-visual. Implicit aggregations are re-chosen at every drag — the trap Lesson 5 is built around.
Meaning, not mechanics: a duplicated roster row restates one instructor; a duplicated sales line can be a second real sale. Ask what a row represents before deleting any.
Uniqueness of the key column on the "one" side. Seven InstructorIDs appear twice, so validation fails until the dedup.
The number is right, the format is wrong. Set percent format on the measure — do not multiply by 100 in the formula, or the number becomes wrong instead.
CALCULATE replaces (or adds to) the current filter context before evaluating; plain SUM can only accept the context it is handed.
Slicer → the sessions table it filters → across relationships in the single allowed direction → every measure that reads a filtered table.
Bar groups by District (studios table) → filter flows studios → sessions → measure sums fees of just that district's completed sessions.
Open Table view and read rows; check row counts and key uniqueness before trusting any visual. The canvas is not the data.

Scoring your rehearsal

No grade — but be honest about three things: Did you finish inside 75 minutes? Did every measure match to the cent? Did you run the key check before the relationship dialog complained? A yes to all three means the real midterm is a formality. A no tells you exactly which lesson to reread, tonight and not the night before.