Measures that mean something

Everyone who opens Power BI hits the same wall: "should this be a calculated column or a measure?" and later, "why does my number change when I click a slicer?" Both walls are the same wall. Once you see that a measure re-runs inside whatever filters surround it, DAX stops being incantations and becomes one repeated move. We build that intuition on Daybreak, in the browser, with the SQL visible - because the generated GROUP BY is filter context wearing its work clothes.

Every calculation in a BI model runs at one of two moments. A calculated column runs once per row when the data refreshes, and the result is stored in the table - line_revenue = quantity * price is the classic. A measure runs at query time, inside the filters of whoever is looking - Total Revenue = SUM of line_revenue. Rule of thumb, worth the whole session: if it aggregates, it is a measure. Columns are for row-level labels and keys; measures are for every number a human reads on a dashboard.

Here is the secret that makes a matrix visual work: every cell is the same measure, evaluated under that cell's own filters. The Coffee row filters to Coffee, the March column filters to March, the plan slicer filters everything - and [Revenue] re-runs inside each combination. That set of active filters is the filter context. And CALCULATE is simply the function that says: run this measure, but with changed filters - add one, replace one, or ignore one, regardless of what the visual is doing.

Watch it happen. Each bar below is one measure - revenue - evaluated under a different category filter. Now press Show SQL: the GROUP BY you see is the filter context made visible. One declaration, four evaluations.

Once filter context clicks, time intelligence is just CALCULATE editing the date filter: MTD stretches it to "month so far", YTD to "year so far", YoY swaps it for "same period last year". All of it leans on the date table you built in b3 - no proper date table, no time intelligence. But some measures need more care than a date filter: semi-additive measures sum happily across some dimensions and lie across others.

Exhibit A: distinct customers. A customer who orders in February and March is one customer, not two - so you may not add monthly customer counts together. Watch the monthly line, then let SQL catch the lie:

SELECT 'sum of 6 monthly distinct counts' AS method,
       SUM(monthly_customers) AS customers
FROM (SELECT COUNT(DISTINCT customer_id) AS monthly_customers
      FROM orders
      GROUP BY strftime('%Y-%m', order_date))
UNION ALL
SELECT 'distinct customers, Jan-Jun as one period',
       COUNT(DISTINCT customer_id)
FROM orders;

A governed measure is a definition card: name, formula in business words, filters included and excluded, aggregation traps, owner. Tonight we write four of them for Daybreak and test each against the live database. This little document IS the semantic model - the same "one number, one truth" contract the leader track builds in a3, written by the people who compute it.

SELECT ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue_all_orders,
       ROUND(SUM(CASE WHEN o.status = 'completed'
                      THEN oi.quantity * oi.unit_price
                      ELSE 0 END), 2)               AS revenue_completed_only
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id;

Three exercises, rising difficulty: read a chart with a data wrinkle, prove a pitfall with SQL, and write your first CALCULATE. Get the third one right and you have written the pattern behind half of production DAX.

SELECT ROUND(AVG(monthly_aov), 2) AS avg_of_monthly_aovs,
       (SELECT ROUND(SUM(oi.quantity * oi.unit_price) * 1.0
                     / COUNT(DISTINCT o.order_id), 2)
        FROM orders o
        JOIN order_items oi ON oi.order_id = o.order_id) AS true_overall_aov
FROM (
  SELECT SUM(oi.quantity * oi.unit_price) * 1.0
         / COUNT(DISTINCT o.order_id) AS monthly_aov
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY strftime('%Y-%m', o.order_date)
);

• Write definition cards for your team's three core measures: name, formula in business words, what is included and excluded, owner. For each, note the semi-additive traps - can it be summed across time? across customers? If not, write the correct aggregation on the card.
• Find one ratio metric in the wild being averaged wrongly - an average of averages in a spreadsheet, a dashboard averaging monthly percentages, a KPI that mixes levels. You will find one faster than you expect. Compute the divide-last version and note the gap.
• In the playground, pick every measure with Month as the dimension and read the generated SQL until the filter-context story is boring to you.
• Bring to b5: your ugliest real-world chart. Next session is chart choice, and we will fix it properly.

Tonight was the conceptual core of the DAX and calculations domain, tool-agnostic and testable in the browser. Exact function syntax, click-paths, and the deeper evaluation-context rules stay with the vendors. This page covers: