Power BI starter: measures that match the question

A manager asks, “What was net revenue for East last month, ignoring returns that were still open?” You drag net_amount onto a card, add two slicers, and hope. Sometimes that works. Often it almost works, which is worse: the number is close enough to survive the meeting and wrong enough to steer a decision. Columns are raw material. Measures are answers. This part is about writing answers that match the question you were actually asked.

This is Part 2 of Power BI starter. Part 1 put the star schema and grain first. Here we build measures with DAX that respect filter context, name themselves after the business question, and fail loudly when the model cannot support them. Part 3 covers publish, share, and trust once the numbers deserve an audience. Related AMS reading: Metrics that matter, the SQL series for the warehouse side of definitions, and Learn for the full map.

What you’ll learn

Why implicit column aggregates are a trap for shared reports
How filter context and row context differ in everyday language
Core measure patterns: simple sum, distinct count, ratio, and time comparison
When to use CALCULATE, and how to keep it readable
A worked set of sales measures with tests you can run on a blank page
Common DAX mistakes and a practice loop for this week

Measures versus columns, without the mystique

A column stores a value per row (or a calculated value computed per row when the model processes). A measure computes a result for the current filter context: whatever slicers, row headers, and filters the visual is applying right now. Dragging net_amount into a visual and letting Power BI “Sum” it creates an implicit measure. That is fine for a private experiment. For anything shared, prefer an explicit measure with a name people can search and a definition people can read.

Why bother?

One definition. “Net Revenue” means the same formula on every page.
Room to grow. Today a sum, tomorrow a sum with a status filter and a currency rule.
Reviewability. Code review and documentation attach to a named object, not a silent default.
Hiding raw fields. You can hide net_amount from report view and force authors through the measure.

Rule of thumb: If two people might sum the same column differently (include tax or not, open returns or not), it is a measure with a written rule, not a default aggregate.

Filter context in plain English

When a visual asks a measure for a number, Power BI already has a set of filters active: year 2026, region East, category Stationery, maybe a page-level filter for “status = shipped.” That package is the filter context. Your measure runs inside it unless you deliberately change it with functions like CALCULATE.

Row context is different. It appears when DAX walks row by row, such as in a calculated column or inside an iterator like SUMX. Beginners often write a calculated column when they needed a measure, or use an iterator when a simple SUM was enough. Start with measures and simple aggregations. Reach for iterators when the business rule truly needs “for each row, do X, then aggregate.”

A practical test: change a slicer. If the number should change and does not, your measure is ignoring filter context (or the field is not related). If the number changes when it should not (for example a “company total” KPI that still filters by the row’s region), you need to remove or adjust filters on purpose, usually with CALCULATE and ALL / REMOVEFILTERS patterns.

Filled measure discipline: business definition, named DAX measure, test cases, then the visual

Name measures after the question

Bad names: Measure 1, Sum of net_amount, rev2_final. Good names sound like the sentence a stakeholder used: Net Revenue, Orders, Avg Order Value, Net Revenue Prior Year, Return Rate %. Put units in the name or in the format string, not only in a chart title that someone will delete.

Keep a short dictionary on an “About metrics” page or in your team wiki:

Measure name	Business meaning	Grain / notes
Net Revenue	Sum of net_amount on order lines	Line grain; excludes tax in this model
Orders	Distinct order_id	Do not count lines
Avg Order Value	Net Revenue / Orders	Blank if Orders is 0
Units	Sum of quantity	Line grain

If Finance and Sales disagree on “revenue,” do not encode the fight in three silent measures with similar names. Surface the conflict, pick a definition for this report, and label it. Cross-link to your company’s metric owner when you have one. AMS treats metrics as contracts for a reason: see Metrics that matter.

Core patterns you will reuse

1. Simple aggregation

Net Revenue =
SUM ( fact_order_lines[net_amount] )

Use this when the fact column already matches the business rule. Format as currency.

2. Distinct count for entities

Orders =
DISTINCTCOUNT ( fact_order_lines[order_id] )

Counting rows counts lines. Counting distinct order_id counts orders. Say the grain out loud every time you choose.

3. Safe ratios

Avg Order Value =
DIVIDE ( [Net Revenue], [Orders] )

DIVIDE avoids divide-by-zero explosions. Prefer it over the / operator for ratios that can hit empty filters.

4. CALCULATE to shift the question

Net Revenue Stationery =
CALCULATE (
    [Net Revenue],
    dim_product[category] = "Stationery"
)

CALCULATE modifies filter context, then evaluates the expression. You can add filters, remove filters, or swap relationships. Readable CALCULATE calls beat nested cleverness. If a measure needs a paragraph of comments to explain, split it into intermediate measures.

5. Time intelligence with a real date table

Net Revenue Prior Year =
CALCULATE (
    [Net Revenue],
    SAMEPERIODLASTYEAR ( dim_date[date] )
)

This only behaves if dim_date is marked as a date table, covers the needed continuum of days, and relates correctly to the fact. Time intelligence is not a substitute for a broken date dimension. Part 1’s date work is the tax you pay for these functions.

Worked example: measures for the sales starter

Reuse the Part 1 toy model: fact_order_lines, dim_date, dim_customer, dim_product. Business questions for this page:

What is net revenue?
How many orders?
What is average order value?
What share of net revenue is Stationery?

Measure set

Net Revenue =
SUM ( fact_order_lines[net_amount] )

Units =
SUM ( fact_order_lines[quantity] )

Orders =
DISTINCTCOUNT ( fact_order_lines[order_id] )

Avg Order Value =
DIVIDE ( [Net Revenue], [Orders] )

Net Revenue Stationery =
CALCULATE (
    [Net Revenue],
    dim_product[category] = "Stationery"
)

Stationery Revenue Share =
DIVIDE ( [Net Revenue Stationery], [Net Revenue] )

On the toy data from Part 1, with no filters: Net Revenue = 145, Orders = 2, Avg Order Value = 72.5, Stationery share = 100% (both products are Stationery). That last result is intentional: your test page should make “boring correct” numbers easy to see before you add messy categories.

Test matrix on a blank page

Build a matrix visual with dim_customer[region] on rows and the measures on columns. Then a second matrix with dim_product[product_name]. Then cards for the totals. You are looking for internal consistency, not beauty.

Check	Action	Expected on toy data
Total revenue	Card with Net Revenue	145.00
Orders vs lines	Orders card vs row count	2 orders, 3 lines
Region East	Slicer East	Net Revenue 65.00, Orders 1
Region West	Slicer West	Net Revenue 80.00, Orders 1
AOV math	Compare AOV to Revenue/Orders	Matches DIVIDE result
Share bounds	Stationery Revenue Share	Between 0 and 1 (format as %)

Matrix of measures by region showing East 65 and West 80 with matching order counts and average order value

When the question needs USERELATIONSHIP

Suppose the fact also has ship_date_key with an inactive relationship to dim_date. Revenue by ship month is a different question than revenue by order month.

Net Revenue by Ship Date =
CALCULATE (
    [Net Revenue],
    USERELATIONSHIP ( fact_order_lines[ship_date_key], dim_date[date_key] )
)

Label the measure so nobody thinks it is the default. Put a note on the page: “Uses ship date, not order date.” Silent date role swaps are how finance and ops start separate wars.

Calculated columns: use sparingly

Calculated columns are computed at refresh and stored. They are good for static labels derived from other columns (a simple status band, a concatenated display name) when you cannot push the logic upstream. They are a poor home for “total revenue” style logic, because they do not recompute with visual filters the way measures do.

If you catch yourself writing a calculated column that sums something, stop. You almost certainly want a measure. If you need a flag used as a slicer (for example “Is High Value Customer”), a column or an upstream attribute can be appropriate. Prefer fixing the dimension in Power Query or the warehouse when the flag is durable business logic.

Variables make DAX readable

As measures grow, VAR / RETURN keeps intent visible:

Avg Order Value =
VAR Revenue = [Net Revenue]
VAR OrderCount = [Orders]
RETURN
    DIVIDE ( Revenue, OrderCount )

This style also helps debugging: temporarily return an intermediate variable to see which piece is blank. Blank is not always wrong. Empty filter context should often return blank, not zero, so charts do not draw misleading zeros. Choose zero only when zero is a true business fact.

Common mistakes

Counting lines when you meant orders. COUNTROWS versus DISTINCTCOUNT of the entity key.
Using the slash operator for ratios. Prefer DIVIDE and think about empty sets.
CALCULATE piled six filters deep with no intermediate measures. Split for humans.
Time intelligence without a proper date table. Functions will “work” and still lie.
Two measures named almost the same with different filters. Rename until the difference is obvious.
Business logic only in chart-level filters. The next page forgets the filter and “metrics drift.”
Ignoring unit tests on a blank page. If you did not check East versus West on known data, you are cosplaying confidence.

How to practice this week

Day 1: Take three questions from a real stakeholder Slack thread. Write measure names before you write DAX.
Day 2: Implement simple sum, distinct count, and ratio measures on your Part 1 model. Hide raw amount columns from report view.
Day 3: Build the test matrix. Force a wrong distinct count on purpose, screenshot the wrong total, then fix it. Keep the screenshot in your notes.
Day 4: Add one CALCULATE measure that encodes a real business rule (one category, one status, one channel).
Day 5: Write a five-line dictionary for your measures and paste it into a text box on an About page. Share with one teammate and ask them to break your definitions.

Quick recap

Measures answer questions inside filter context; columns store row values.
Name measures after the business question and keep a short dictionary.
Master sum, distinct count, DIVIDE, and small CALCULATE patterns before clever DAX.
Time intelligence depends on a real marked date table and honest relationships.
Test on a blank page with known toy or reconciled totals before you decorate.

Next: Part 3 publishes the dataset and report without confusing “shared” with “trusted.” Workspaces, refresh, RLS, and a short launch checklist.

Sources

Microsoft Learn: Create measures for data analysis in Power BI Desktop
Microsoft Learn: CALCULATE function (DAX)
Microsoft Learn: DIVIDE function (DAX)
Microsoft Learn: SAMEPERIODLASTYEAR function (DAX)
SQLBI: Row context and filter context in DAX