Day 37. DAX that is correct, not just working

⏱ 4h · learn 80m · lab 130m · ship 15m · review 15m🏷 restaurants, hotels, marketingBefore: Day 36

By the end of today you can

Explain filter context, row context and context transition
Use CALCULATE, filter modifiers and variables confidently
Build time intelligence (YTD, last year, rolling) on a proper date table
Write ratio, semi-additive and distinct-count measures that aggregate correctly

1. Why this matters

DAX is short to write and easy to get subtly wrong. Measures that look right on a card can be wrong in a table, or right per day and wrong per month. Today's patterns are the ones that real reports need and interviews test.

2. Measures, not calculated columns

A measure is computed at query time in the current filter context. A calculated column is computed at refresh, row by row, and stored (bigger model). Rule: numbers you aggregate → measures; attributes you slice by → columns (ideally from dbt, not DAX).

Net Sales = SUM ( fct_order_lines[line_revenue] )
Orders = DISTINCTCOUNT ( fct_order_lines[order_id] )
Average Check = DIVIDE ( [Net Sales], [Orders] )          -- ratio of sums at any grain, safe divide

3. Filter context and CALCULATE

Every cell in a visual has a filter context (the slicers, rows, columns that apply). CALCULATE evaluates an expression in a modified filter context.

Net Sales Delivery = CALCULATE ( [Net Sales], dim_channel[channel] = "delivery" )
Share of Brand =
VAR brand_total = CALCULATE ( [Net Sales], REMOVEFILTERS ( dim_menu_item ), VALUES ( dim_restaurant[brand] ) )
RETURN DIVIDE ( [Net Sales], brand_total )

Context transition: inside an iterator (SUMX, AVERAGEX), calling a measure turns the current row into a filter. That's why AVERAGEX ( VALUES ( dim_date[date_key] ), [Net Sales] ) gives the average daily sales.

4. Time intelligence

Requires a marked date table with contiguous dates.

Net Sales LY = CALCULATE ( [Net Sales], SAMEPERIODLASTYEAR ( dim_date[date_key] ) )
Net Sales YTD = TOTALYTD ( [Net Sales], dim_date[date_key], "04-30" )          -- fiscal year ends 30 April
Net Sales 7d = CALCULATE ( [Net Sales], DATESINPERIOD ( dim_date[date_key], MAX ( dim_date[date_key] ), -7, DAY ) )
Net Sales Same Weekday LY =
CALCULATE ( [Net Sales], TREATAS ( VALUES ( dim_date[same_weekday_last_year] ), dim_date[date_key] ) )
YoY % = DIVIDE ( [Net Sales] - [Net Sales Same Weekday LY], [Net Sales Same Weekday LY] )

For QSR and hotels, same weekday last year (364 days back) is usually the fairer comparison than the same calendar date.

5. Semi-additive and hotel measures

Rooms Available = SUMX ( VALUES ( dim_date[date_key] ), SUM ( dim_hotel[rooms] ) )   -- rooms × nights in context
Room Nights Sold = SUM ( fct_stay_nights[room_nights] )
Occupancy = DIVIDE ( [Room Nights Sold], [Rooms Available] )
ADR = DIVIDE ( SUM ( fct_stay_nights[room_revenue] ), [Room Nights Sold] )
RevPAR = DIVIDE ( SUM ( fct_stay_nights[room_revenue] ), [Rooms Available] )
OTB Latest = CALCULATE ( SUM ( otb_snapshot[room_nights] ), LASTDATE ( otb_snapshot[as_of_date] ) )

Maths and principle

Principle: a measure is a function of the filter context. Totals are not the sum of rows; they're the same function evaluated on a bigger context.

Equation: for a ratio measure $r(F) = \dfrac{\sum_{F} a}{\sum_{F} b}$, the total row computes $r(F_1 \cup F_2) \neq r(F_1) + r(F_2)$ and $\neq \tfrac12(r(F_1)+r(F_2))$ in general. That's correct behaviour, and why visual totals sometimes "look wrong" to users.

Analogy: evaluating density on a bigger volume: total mass over total volume, not the sum of densities. Where it breaks: when a business user wants a sum of per-row results (sum of per-store targets met). Then write that explicitly with SUMX over stores.

Try it: two stores. A: sales $1,000, orders 50. B: sales $3,000, orders 100. Average Check total? ($4,000/150 = $26.67, not $25 = mean of 20 and 30.)

The 5-year lens

Keep every measure in a display folder with a description (the definition from your metric dictionary). Use variables for readability and speed. Use DIVIDE, never /. Format strings in the model, not per visual. And test measures in a matrix by date × store against a SQL query on the warehouse; DAX bugs hide in totals and blanks, so check the total row and a day with no sales.

Lab

Build 25 measures in _Measures: QSR (Net Sales, Orders, Average Check, Items per Order, Food Cost %, Channel Share, Same-Store flag measure), time intelligence (LY, same weekday LY, YoY %, YTD fiscal, 7-day, 28-day), hotel (Rooms Available, Occupancy, ADR, RevPAR, OTB Latest, Pickup 7d), marketing (Spend, ROAS, CPA, CTR).
For each, add a description and display folder.
Verify 5 of them against SQL on the warehouse for one month and one store. Record pass/fail in docs/measure-tests.md.
Install DAX Studio; run one slow measure (a FILTER over the whole fact) and rewrite it faster; record timings.
Use USERELATIONSHIP to show bookings by created date and by arrival date in one visual.

Ship

feat(powerbi): 25 documented measures with SQL cross-checks.