🎯 Business problem

A marketplace wants one view of order economics: revenue, delivery performance, seller quality, and customer satisfaction — across products, sellers, and Brazilian regions.

🧮 Why it’s senior

9 normalized tables force real modeling: orders, order_items, products, sellers, customers, payments, reviews, geolocation, category translation. You must collapse to a clean star, handle a fact at order-item grain, and a separate payments fact — a genuine multi-fact model.

🧱 Model

• Facts: FactOrderItems (item grain: price, freight), FactPayments (payment grain).
• Dims: Date, Customer (+ geolocation), Seller (+ geolocation), Product (+ English category via translation table).
• Two facts share Date & Order dimensions — never relate fact-to-fact.

⚡ Key DAX

On-Time Delivery % =
VAR Delivered = CALCULATE(COUNTROWS(Orders),
   NOT ISBLANK(Orders[delivered_date]))
RETURN DIVIDE(
   CALCULATE(COUNTROWS(Orders),
     Orders[delivered_date] <= Orders[estimated_date]), Delivered)

Avg Delivery Days =
AVERAGEX(Orders,
   DATEDIFF(Orders[purchase_date], Orders[delivered_date], DAY))

🎯 Business problem

Segment customers by value and behavior (RFM), measure retention by acquisition cohort, and estimate customer lifetime value to target marketing.

🧮 Why it’s senior

RFM and cohort retention are pure advanced DAX — virtual tables, dynamic segmentation, and a cohort matrix built without a physical cohort column. This is where mid-level developers stall.

⚡ Key DAX

-- Recency / Frequency / Monetary per customer
Recency Days =
VAR Last = CALCULATE(MAX(Sales[InvoiceDate]))
RETURN DATEDIFF(Last, MAX('Date'[Date]), DAY)

-- dynamic segment using a disconnected band table + TREATAS
Segment Sales =
CALCULATE([Total Sales],
  TREATAS(VALUES(Bands[Segment]), Customer[Segment]))

Build the cohort matrix: rows = acquisition month, columns = months-since-first-purchase, values = retained customers / cohort size.

🎯 Business problem

Find who churns and why. Quantify churn rate, revenue at risk, and the drivers (contract type, tenure, services) so retention can act.

⚡ Key DAX

Churn Rate =
DIVIDE(CALCULATE(COUNTROWS(Customers), Customers[Churn]="Yes"),
        COUNTROWS(Customers))

Revenue at Risk =
CALCULATE(SUM(Customers[MonthlyCharges]), Customers[Churn]="Yes")

📊 Report

Use the Key Influencers and Decomposition Tree AI visuals to surface drivers, tenure cohorts, and a churn-by-contract matrix.

🎯 Business problem

HR wants attrition rate by department, role, and satisfaction — and each manager should see only their org.

🧮 Why it’s senior

The standout skill here is organizational-hierarchy RLS with PATH / PATHCONTAINS, plus field parameters for flexible analysis.

⚡ Key DAX

HierPath = PATH(Employee[EmpID], Employee[ManagerID])

-- RLS role filter: manager sees their whole subtree
PATHCONTAINS(Employee[HierPath],
  LOOKUPVALUE(Employee[EmpID], Employee[Email], USERPRINCIPALNAME()))

Attrition % =
DIVIDE(CALCULATE(COUNTROWS(Employee), Employee[Attrition]="Yes"),
        COUNTROWS(Employee))

🎯 Business problem

Forecast store sales, compare actual vs prior year & vs forecast, and analyze the impact of promotions, holidays, and store type.

🧮 Why it’s senior

Deep time intelligence, a store hierarchy, and the architecture decisions: incremental refresh design on ~1M rows and calculation groups so you don’t write YTD/PY/YoY for every measure.

⚡ Key DAX

Sales PY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YTD = TOTALYTD([Sales], 'Date'[Date])
Sales 4W MA =
VAR W = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY)
RETURN CALCULATE(AVERAGEX(VALUES('Date'[Date]), [Sales]), W)

Use the line chart’s built-in Analytics → Forecast with confidence bands; compare to actuals.

🎯 Business problem

Track delivery performance, late-shipment risk, profit per order, and where the supply chain leaks margin or time.

⚡ Key DAX

Late Delivery % =
DIVIDE(CALCULATE(COUNTROWS(Orders), Orders[LateRisk]=1),
        COUNTROWS(Orders))

P95 Shipping Days =
PERCENTILEX.INC(Orders,
   DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY), 0.95)

📊 Report

Geo map of late-shipment hotspots, profit waterfall by category, and an SLA scorecard. Mention you’d use p95/p99, not averages, for SLAs.

🎯 Business problem

Driver & constructor standings over time, head-to-head comparisons, circuit performance, and pit-stop efficiency.

🧮 Why it’s senior

13 interrelated tables (races, results, drivers, constructors, circuits, lap_times, pit_stops, qualifying) — great for showing relationship design, role-playing handling, and ranking DAX over a big relational set.

⚡ Key DAX

Driver Points = SUM(Results[points])
Driver Rank =
RANKX(ALL(Drivers[name]), [Driver Points], , DESC, Dense)
Avg Pit Stop (s) =
AVERAGEX(PitStops, PitStops[milliseconds]) / 1000

🎯 Goal

Not a pretty report — a performance engineering project. Take millions of rows and make a model that’s small, fast, and refreshable.

🧮 What you practice

• Cardinality reduction: split DateTime into Date + Time, drop precise lat/long precision, remove unused columns — measure size in VertiPaq Analyzer.
• Aggregation tables: pre-summarize by date+zone; verify the engine hits the agg.
• Incremental refresh: partition by date so only recent data reloads.
• DAX tuning: read Server Timings in DAX Studio; kill CallbackDataID; SE vs FE.
• Composite model: import dims + DirectQuery/agg the giant fact.

• ☐ Clean star schema, dedicated Date table marked as date table.
• ☐ Measures named & organized in display folders; no stray calculated columns.
• ☐ Numbers validated against a source SQL/COUNT.
• ☐ At least one senior twist (RLS / calc groups / aggregations / what-if).
• ☐ Report is focused: clear titles, consistent color, mobile layout.
• ☐ README: problem → data → what you built → impact (with numbers).
• ☐ Published to a workspace; refresh works; .pbip committed to GitHub.
• ☐ You can explain every layer and pitch it in 30 seconds.