Midterm Practical — Harbor Lane Clinics

Format. 75 minutes, in one sitting, open notes, closed neighbor. Submit MID_LastFirst.pbix. Worth 60% of the midterm (the concept quiz is the other 40%). The dataset is new — a clinic network, not a retailer — because the skills are supposed to transfer, and this is where we find out.

Scenario

Harbor Lane Clinics runs 8 locations across three regions. You receive one year of appointment data (July 2025 – June 2026), a clean clinic list, and a providers export that — like every providers export in history — has problems. Leadership wants a working model and four honest numbers.

Tasks

Clean the providers export (providers_export.csv, 46 rows). Remove exact duplicates and convert the text "N/A" specialties to true nulls. The clean result has 40 providers.
Load appointments.csv (9,500 rows) and clinics.csv (8 rows). Model: appointments → providers (ProviderID) and appointments → clinics (ClinicID), both many-to-one, single direction. Run the key checks first — one of the three tables cannot be a "one" side; know which and why before the dialog tells you.
Measures (exact names):
- Appointment Count — all appointments: 9,500
- No-Show Rate — no-show appointments over all appointments, DIVIDE, percent format: 7.64%
- Completed Fees — fees for Completed appointments only (hint: this is a CALCULATE with a status filter): $1,967,877.04
- Avg Fee per Completed — composed from measures you have: $224.29
One report page: the four measures as cards, plus a bar of Completed Fees by clinic Region (South leads at $727,914) and a Specialty slicer that moves everything.

Scoring

Component	Points
Providers cleaned: 40 rows, true nulls (not text)	10
Model: two correct relationships, correct direction; no incorrect extras	12
Four measures, exact names, verified values	24
Report page: cards, region bar, working specialty slicer	10
File naming and submission hygiene	4

What transfers Nothing here is new. The providers export is customer_export.csv wearing scrubs; the model is a two-dimension star; No-Show Rate is Return Rate with a status instead of a second fact; Completed Fees is your first graded CALCULATE. If the retailer's skills were really skills, the clinic is a costume change.