Power BI / Advanced: performance and governed delivery / Lesson 18

Capstone: model validation and delivery

Assemble a concrete synthetic sales model review connecting preparation, relationships, DAX, report context, security and freshness to a bounded release decision.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Integrate independent expected results and model invariants.
Classify failures at the earliest broken evidence boundary.
Write a complete release handoff with unexecuted checks explicit.

State the complete fixture

The capstone uses three sales lines: A quantity2 at price10, B quantity1 at price20 and A quantity3 at price10. Product costs are A6 and B12 under a deliberately current-cost contract. Order-date revenue is40 on Jan1 and30 on Jan2. The intended totals are revenue70, gross profit28 and gross margin40%. Product A contributes50. These concrete values make a review falsifiable. They do not represent a real organization or establish a production metric. Write grain, cost meaning, currency and exclusions before building a page so the expected answer remains independent of the report’s appearance.

Build the evidence chain

Review source rows and types, preparation counts, product/date key integrity, active filter paths, measures and visual selections in that order. A merge that repeats A can invalidate revenue before DAX becomes relevant. A missing filter path can repeat70 by product even with the right formula. The first DAX snippet defines margin as a ratio of the approved measures and expects0.4 before percentage formatting. It is a model fragment, not a complete engine test. Compare numerator and denominator independently rather than trusting a plausible40% card that could hide two equally inflated amounts.

dax A dependent metric with separate numerator checks

Gross Margin = DIVIDE([Gross Profit], [Revenue])

Assume Gross Profit28 and Revenue70 from the approved fixture. Intended numeric margin is0.4, displayed as40% through formatting. DAX is unexecuted.

Compute a complete local oracle

The second program calculates revenue, profit and margin with exact Decimal arithmetic. It uses the same public fixture but remains independent of DAX, relationships and report state. A matching set of results is useful local evidence; it does not prove that Power BI loaded the same rows or applied the same cost rule. Keep expected product and date subsets alongside the overall totals. Include a deliberately unmatched product key, zero-denominator case and duplicate merge input as negative cases in a future authorized model review, each with its own expected rejection or result.

python Exact synthetic capstone oracle

from decimal import Decimal
lines = [("A", 2, 10), ("B", 1, 20), ("A", 3, 10)]
cost = {"A": 6, "B": 12}
revenue = sum(quantity * price for _, quantity, price in lines)
profit = sum(quantity * (price - cost[product]) for product, quantity, price in lines)
margin = Decimal(profit) / Decimal(revenue)
print(revenue, profit)
print(f"{margin:.1%}")

Expected output

70 28
40.0%

70,28 and40.0% are computed locally from the stated rows. No model, source, account or service is accessed.

Include consumer security and freshness

A correct unrestricted total is not enough for a release. Define the intended Viewer population, RLS role membership, distribution entitlement and latest usable data cutoff. A North-only consumer should not be judged using the unrestricted70 oracle if the allowed data is50. Likewise, an old import copy can produce correct arithmetic for the wrong business time. Check visual interactions, reset behavior and accessibility under the intended reader journey. No service test is performed here. Separate role logic, actual identity permissions and observed report behavior so each success claim remains tied to evidence rather than a single green publication status.

Deliver an honest release record

The handoff names model and source identities, business definitions, independent expected results, actual local tests, reviewed official references and all unexecuted engine/UI/service checks. It also records ownership, compatibility risks and a supported recovery path for shared-model changes. This course executes only harmless offline programs and publishes no content to Power BI. DAX and M remain runnable:false. A complete learning artifact can still make the next review concrete by identifying source reconciliation, model evaluation, security, freshness and consumer acceptance as separate requirements instead of inventing a successful deployment.

Common errors and fixes

A plausible margin hides duplicated revenue and profit

Why it happens: Only the ratio was checked, so equal inflation canceled out.
How to fix it: Reconcile numerator, denominator and row grain independently.
Prevent it next time: Keep absolute-amount assertions beside ratio assertions.

An unrestricted oracle is used for a restricted viewer

Why it happens: Expected population ignored RLS and consumer context.
How to fix it: Define expected rows and totals for the intended identity/role.
Prevent it next time: Version security acceptance alongside metric acceptance.

Local Python success is reported as a Power BI deployment test

Why it happens: Independent arithmetic was promoted into engine/service evidence.
How to fix it: State exact executed checks and leave platform checks explicitly unexecuted.
Prevent it next time: Attach an evidence boundary to every release claim.

Check your understanding

1. If a duplicate merge doubles revenue and profit, why can margin still look correct?

Show worked solution

Both numerator and denominator are multiplied by the same factor, leaving their ratio unchanged. The40% appearance therefore cannot prove correct facts. Compare70 revenue,28 profit, line count and keys independently, then review the merge cardinality before changing DAX or formatting.

2. What should the final report say when only the offline oracle ran?