A governed Power BI semantic model: the margin bridge and the measure dictionary

Eight work orders, one of them canceled, is the entire dataset behind this model, and it is enough to prove every governance habit that scales to a real one: a single grain, a star schema, five measures that compose instead of duplicate, and a written dictionary that says what each number means. Get any of that wrong on a real report and revenue quietly means one thing on the summary card and something else on the technician page, and nobody can see it happen. This is the layer I build before I build a single visual, and every number below is checkable against the eight rows by hand.

I demonstrate the whole thing on a fictional field-service company I made up for this walkthrough: eight synthetic work orders, small enough to check by hand. No real company, customer, technician, or invoice appears anywhere.

The margin bridge, and the eight rows behind it

Every bar above rolls up from the fact table below. Eight work orders, synthetic and small enough to add yourself and confirm.

WorkOrderID	ServiceDate	CustomerID	TechnicianID	ServiceCategory	Status	Revenue	DirectCost
WO-1001	2025-01-06	C-01	T-01	Repair	Completed	480	300
WO-1002	2025-01-14	C-02	T-02	Install	Completed	1200	900
WO-1003	2025-01-27	C-01	T-01	Maintenance	Completed	260	150
WO-1004	2025-02-03	C-03	T-02	Repair	Canceled	0	0
WO-1005	2025-02-11	C-02	T-03	Install	Completed	1500	1100
WO-1006	2025-02-19	C-04	T-01	Maintenance	Completed	300	160
WO-1007	2025-02-24	C-03	T-03	Repair	Completed	540	360
WO-1008	2025-03-05	C-01	T-02	Install	Completed	1300	950

One row, WO-1004, is canceled at zero revenue and zero cost. That single canceled row is why a completed-order count and a plain row count disagree later in this post, and it is the first example of the model reporting the same fact two different ways depending on which filter is in place.

What the numbers say

• Revenue across all eight rows is $5,580. Direct cost is $3,920. Gross profit is $1,660, a 29.7 percent margin.
• Completed Orders counts 7, not 8, because WO-1004 was canceled and the measure filters to completed status before it counts.
• Average Ticket, revenue divided by completed orders, is $797.14.
• Grouped by month instead of by status, revenue was $1,940 in January, $2,340 in February, and $1,300 in March. Year to date through February is $4,280.
• Month over month, February came in 20.6 percent above January. March came in 44.4 percent below February, on a fixture with only three months in it.

Why this report matters

• One Revenue measure feeds every visual on the report, so revenue cannot mean one thing on a summary card and a subtly different thing on a technician's page. There is exactly one place the definition can drift.
• A measure that divides safely returns a blank instead of breaking when a filter narrows down to a slice with zero revenue, so a margin chart does not fail the moment someone drills into an empty month.
• The measure dictionary is what I hand a reviewer who challenges a number. It answers what a metric means and what filter rule it follows, in writing, instead of in a hallway conversation.
• A Performance Analyzer log turns "the report feels slow" into a specific measure and a specific millisecond figure, which is the difference between a guess and a fix.

Start from the grain

The first decision in any model is the grain: what does one row of the fact table mean? Here it is one row per completed-or-not work order, the eight rows above. Get the grain wrong and every measure downstream inherits the confusion, because a sum only means something once you know what you are summing over.

Four dimensions sit around that fact, each on the one side of a one-to-many relationship with the fact on the many side, filtering in a single direction:

• DimDate: a contiguous calendar, one row per day, covering the whole range.
• DimCustomer: C-01 through C-04.
• DimTechnician: T-01 through T-03.
• DimServiceCategory: Repair, Install, Maintenance.

That is the star: one fact table in the middle, dimensions radiating out, filters flowing inward. Dimensional modeling theory (grain, surrogate keys, cardinality, why a star beats one wide flat table) is well covered ground elsewhere, so I keep this section short and let the rest of the post do the teaching. My synthetic-data clustering write-up works a neighboring modeling problem on a different made-up book, if the comparison is useful.

Base measures, composed instead of duplicated

I keep a model maintainable by writing exactly two base measures, the ones that touch the fact table's raw columns directly: a running total of revenue and a running total of direct cost. Everything else composes on top of those two by referencing them by name instead of touching the columns again. Gross profit is revenue minus direct cost. Gross margin percent is gross profit divided by revenue. Neither one sums a column a second time.

That division is the one place a model breaks quietly if you are not careful. A plain division does not raise an error when a filter narrows a slice down to zero revenue. It returns a result no visual can plot cleanly. I use a safe-divide function for every measure-level division instead, so an empty slice returns a clean blank rather than a broken chart.

Tools I would use: Power BI Desktop for authoring, DAX measures instead of calculated columns wherever the logic can live in one, and a hidden page in the report for the measure dictionary itself.

Completed Orders is a different kind of measure. Instead of summing, it narrows the rows to completed status first, then counts the distinct work orders left in that narrowed set. That is the whole demonstration of a filtered measure in one line: the same table, a different answer, because the filter changed before the count ran. Average Ticket composes on top of both, revenue divided by Completed Orders, using the same safe-divide rule as the margin measure above.

Filter context, taught with one measure

Revenue is defined exactly once, as a sum over the fact table's revenue column, and that definition never changes. Watch what it reports depending on where it lands:

• On the grand total, with no dimension filtering it, Revenue is $5,580.
• On the T-01 row of a technician table, Revenue is $1,040, that technician's three orders.
• On the February row of a month table, Revenue is $2,340.
• On the Install row of a category table, Revenue is $4,000.

Four different numbers, one formula. The measure did not change. The set of rows Power BI evaluated it over changed, because each cell of a visual applies its own coordinates (this technician, this month, this category) as filters before the measure runs. That is filter context. Once it clicks, most confusing DAX results stop being mysterious: the formula is almost never wrong, the context you did not realize you were in usually is.

Monthly revenue, and the marked date table under it

Time-shifting measures, year to date, prior period, a rolling window, all work by reshaping the date filter, not by doing new arithmetic. They need a real calendar to reshape, which is why the first step is not a measure at all: build the date dimension with a contiguous date column and no gaps, then explicitly mark it as the model's date table. Marking it tells the engine which table carries the calendar, so a year-to-date or prior-period measure knows which column to walk.

Grouped by month instead of by status or technician, this is what the same Revenue measure reports: $1,940 in January, $2,340 in February, $1,300 in March. A year-to-date measure replaces the current date filter with every day from January 1 through the last visible date, so on the February row it adds January and February together and lands on $4,280. A prior-period measure shifts the whole date filter back one step, so February's month-over-month result compares $2,340 against January's $1,940, a 20.6 percent increase, and March compares $1,300 against February's $2,340, a 44.4 percent decrease.

I also define a rolling twelve-month measure the same way, as a trailing window ending at the latest visible date. On this three-month fixture the window simply spans the whole range, so it would not tell you anything a real multi-year model would show. The definition is what I would demonstrate on real data, not on three months of it.

The measure dictionary

A model is a governance artifact, not just a pile of formulas, and the cheapest governance I can add is a table that says in plain language what each measure means and what filter rule it follows. I keep this on a hidden page in the report and in the repository. It is the document I would hand a reviewer who asks what exactly average ticket means.

Metric	Plain-language definition	Grain / filter rule
Revenue	Total billed amount on work orders in context	Sum of the fact revenue column; no status filter
Direct Cost	Total direct cost on work orders in context	Sum of the fact cost column; no status filter
Gross Profit	Revenue left after direct cost	Revenue minus Direct Cost, both in the same context
Gross Margin %	Share of revenue kept as gross profit	Gross Profit divided by Revenue with a safe-divide rule (blank when Revenue is 0)
Completed Orders	Count of distinct work orders with status Completed	Distinct count of WorkOrderID filtered to Status = Completed
Average Ticket	Average revenue per completed order	Revenue divided by Completed Orders
Revenue YTD	Revenue from January 1 to the last date in context	Year-to-date total over the marked date table
Revenue MoM %	Month-over-month change in Revenue	Current month versus the prior month

The value is not the table itself. It is that the definition and the measure agree. When someone disputes a number, this is where the conversation starts, and if the DAX has drifted from the dictionary, that mismatch is the bug, not the number.

A repeatable Performance Analyzer review

The right way to make a report faster is to measure, change one thing, and measure again, not to guess. Power BI ships the tool for this built in: turn on Performance Analyzer, start recording, then interact with the page. Each visual logs how long its query against the model took and how long the display took to render, in milliseconds. The query duration is the number that points at the model; a slow render usually points at the visual instead.

The loop I run is deliberately boring:

1. Record a baseline. Refresh the visuals and write down the query duration for the ones that feel slow.
2. Change exactly one thing: a missing relationship, a measure rewritten to lean on a base measure, a calculated column pushed into a measure, one visual removed from the page.
3. Clear the log, record again, and compare the same visual's query duration to its baseline.
4. Log the actual before and after. If it did not move, keep the number that proves it did not move and try a different change.

That log is the deliverable, not a benchmark. On an eight-row model every duration is trivially small. On a real model with millions of fact rows, the same loop is how you find the measure that scans the whole table when it did not need to. If I ever say a change cut the query time in half, it is because Performance Analyzer showed me both numbers, and I would show them too.

My process on the job

• I nail the grain first. Every measure downstream inherits it, so I do not move on until I can say in one sentence what a single row of the fact table means.
• I build a star schema around that grain: one fact table, dimensions radiating out, relationships filtering in a single direction.
• I write exactly two raw-sum base measures, one per fact column that needs summing, and I compose every other measure by referencing those two by name instead of touching the columns again.
• I use a safe-divide function, never the plain division operator, for every division inside a measure, so a filter that narrows to zero revenue returns a blank instead of an error or an infinite value.
• I mark the date table before I write a single time-shifted measure. Nothing built on a calendar works until the engine knows which table is the calendar.
• I treat time intelligence as reshaping the date filter, never as new arithmetic. Year to date, prior period, and a rolling window are the same measure evaluated over a different set of dates, not three different formulas.
• I publish a measure dictionary next to the model, and I treat any disagreement between the dictionary and the DAX as a bug in the model, not a note to update later.
• I run one Performance Analyzer pass before I call a report finished, and I log the real before-and-after numbers instead of a guess about whether a change helped.

Key takeaways

• Pick the grain first. Here it is one row per work order, and every measure downstream inherits that choice.
• Two base measures compose into everything else, so each definition lives in exactly one place, and a safe-divide rule keeps an empty filter from breaking a visual.
• Filter context is why one formula reports different numbers in different cells: the formula rarely changes, the context usually does.
• Time intelligence reshapes the date filter. Revenue moved from $1,940 in January to $2,340 in February, a 20.6 percent gain, then to $1,300 in March, a 44.4 percent drop, all from the same Revenue measure.
• A measure dictionary and a Performance Analyzer log are artifacts I maintain, not one-time documents. A mismatch or an unmeasured slowdown is a bug either way.

Related posts

Other walkthroughs built on the same invented field-service company:

• Putting the Power BI project under version control, so a measure edit gets a diff, a review, and an audit trail.
• Tracing gross margin from raw record to dashboard, where a DAX card and a SQL aggregate are reconciled against each other.
• Six enterprise SQL patterns for dedup, idempotent loads, and a Type 2 dimension.
• The monthly gross-margin bridge report with bronze, silver, and gold layers and an idempotent MERGE.
• Machine learning on the same lakehouse: a legible completion model and a customer segmentation.