6.1Guided case studies
The essentials in 30 seconds
- Assemble: raw export → Power Query (typed cleaning) → star schema (Sales facts + Products, Customers, Cities dimensions + date table) → DAX measures → visuals → publication in the Service.
- Prove: measure with DIVIDE and VAR/RETURN, prior-year revenue and YTD via the marked date table, connected slicers, totals that reconcile — what isn't verified doesn't exist.
- Secure: dynamic RLS tested with « View as », performance timed in the Performance Analyzer, distribution via an app — a production report, not a demo.
Zineb's thread — two deliveries in Algiers
Zineb, a management controller at an agri-food SME in Algiers, reviews her two standout projects of the quarter: the sales dashboard delivered to her management (raw export, Power Query cleaning, star schema, DAX measures, prior-year revenue and YTD, staging, publication with scheduled refresh), then the HR report on headcount and payroll secured with RLS, where each manager only sees their own department. The two cases below pick up her sequence of levels 0 to 5: connect, clean, model, measure, visualize, secure, publish.
Each case follows the flow Context → Steps → Result: replay it on your own data.
Why guided cases
The previous levels laid the bricks (connection, Power Query, star schema, DAX, time intelligence, interactivity, design, RLS, performance, publication); here we assemble them under real conditions. It's the chaining of the right reflexes — a clean model, explicit measures, meaningful visuals, a secured and refreshed report — that separates a production deliverable from a makeshift build.
Replay end to end
A case isn't copied: it's replayed. We return to the production order, adapt the columns, measures and visuals to our own data, and keep the same demand for proof at every step.
From the workshop to production
The real test of a report isn't « the visual shows up », but « it holds up in production »: stable relationships, correct time intelligence, verified RLS, timed performance and a successful refresh in the Service.
Case 1 — An end-to-end sales dashboard
Context: The management report that Zineb hands in every month has to be delivered to executives: revenue, prior-year change, top products, performance by region, from raw data to publication.
Déroulé :
- Connect and clean: import the sales (Excel/CSV), type the columns, remove errors and duplicates in Power Query.
- Model as a star: Products, Customers, Cities dimensions linked to the Sales facts in 1:*, plus a dedicated date table (CALENDARAUTO) marked as a date table.
- Measure: a foundation of measures (Total Revenue, Sales Count, Average Basket via DIVIDE, stored in a « _Measures » table), then YTD revenue, prior-year revenue and change in value and in percentage.
- Visualize and publish: KPI cards, trend line, histogram by region, City/Date slicers; in-house theme, then publication in the Service with daily scheduled refresh.
Result: A complete sales dashboard, modeled as a star, rich in time intelligence, cleanly styled, published and automatically refreshed — the core deliverable of the management-control job.
Case 2 — An HR report secured with RLS
Context: Distribute a headcount and payroll report to managers, each one only seeing their own scope (their department), with a single model.
Déroulé :
- Model: Payroll/Headcount facts, Employees, Departments and Dates dimensions — a clean star, one unique row per department.
- Measure: payroll, headcount (COUNTROWS), average cost (DIVIDE), each department's share (CALCULATE + ALL).
- Secure with RLS: a security table (Email → Department) linked to the Departments dimension, dynamic role Security[Email] = USERPRINCIPALNAME().
- Test and distribute: « View as » simulating several manager emails, then publish and assign the managers to the RLS role in the Service via an app.
Result: A single HR report where each manager only sees their own scope (dynamic RLS tested), cleanly distributed via an app — the standard for sensitive deployments.
Case 3 — A production-tracking report that refreshes itself
Context: Zineb tracks the production lines of the agri-food SME: output per day and per product, with a report that must update itself every night.
Déroulé :
- Connect the most reliable source (SharePoint/OneDrive preferably, for refresh without a gateway) and clean in Power Query.
- Model as a star: Production facts, Products, Lines, Date dimensions; a marked date table for the running totals.
- Measure: quantity produced, rate (DIVIDE), gap to plan, month-to-date running total; KPI cards and histogram by line.
- Publish and make reliable: Performance Analyzer passed, useless columns removed, scheduled refresh, source .pbix backed up.
Result: A production report that refreshes every night, optimized on evidence, and whose flagship measure reconciles with the source.
Ready-to-say script — scoping a Power BI delivery (30s)
« Our need: [sales dashboard / HR report]. Our data: [typed Power Query, star schema + date table]. Our measures: [Total Revenue, prior-year revenue, YTD via DIVIDE and VAR]. Our proof: [RLS tested, Analyzer, scheduled refresh]. »
Keep it to scope a report request with your management.
Ready-to-say script — explaining your dashboard (30s)
« I start from a raw export, I clean in Power Query, I model as a star, I measure in DAX, I stage it with an accent, I secure it if needed and I publish with refresh. »
Useful to explain your approach without technical jargon.
Copy-paste template — steering measures
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Revenue PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dates[Date]))
Growth % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY])
Average Basket = DIVIDE([Total Revenue], [Sales Count])Copy-paste template — dynamic RLS
Security = SUMMARIZE(Employees, Employees[Email], Employees[Service])
-- Relationship Security[Service] → Services[Service]
-- Role (Services secured):
Security[Email] = USERPRINCIPALNAME()SME / small budget
A single case replayed small: one typed source, a two-table star schema, three measures, written proof.
SME Algiers
Adapt to the SME's real exports: French columns, amounts in dinars, SharePoint source to refresh without a gateway.
Remote / hybrid
Double the traceability at a distance: named source, refresh date and written test proof next to the report.
Intercultural
Make the labels explicit before transposing: a label or a date format that is obvious to one person must speak to any newcomer.
48h challenge — apply tomorrow
Take 1 real export and replay the sequence: typed Power Query, star schema with 1 marked date table, 3 DAX measures and one visual published with refresh.
- I can chain Power Query, star schema, DAX and publication on a real case.
- I have a replayable chain (marked date table, named measures, refresh).
- I did the 48h challenge or scheduled its date.
Zineb delivers her sales dashboard. Which sequence follows the guide?