The order we build Power BI projects in

Every Power BI project that goes bad goes bad the same way: the report looks finished long before the model is. Numbers reconcile in one visual and not the next, a refresh takes eleven minutes for no visible reason, and nobody can say which measure is canonical. What follows is the order we build things in, and why.

None of this is exotic. Most of it is in Microsoft's own guidance — it just rarely survives contact with a deadline.

The semantic model is the deliverable

A dashboard is a view over a model. If the model is right, a new dashboard takes an afternoon; if it is wrong, every dashboard is a negotiation. So the first two weeks of an engagement go into the model, and stakeholders see very little.

Concretely, that means agreeing on:

• One date table, marked as a date table, covering every fiscal calendar the business actually uses.
• Star schema by default. Snowflake only where a dimension is genuinely shared and genuinely large.
• Single-direction relationships, with the exceptions written down and justified.
• One owner per measure, named, in the description field.

Modelling: mark the date table, then stop improvising

An unmarked date table is the single most common cause of time intelligence quietly returning the wrong number. Mark it, then build the calendar measures once, on top of it.

Sales YTD =
CALCULATE (
    [Sales],
    DATESYTD ( 'Date'[Date], "6-30" )
)

Sales YoY % =
VAR Current = [Sales]
VAR Prior =
    CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    DIVIDE ( Current - Prior, Prior )

Note the DIVIDE instead of / — it is not style, it is the difference between a blank and an error propagating through six visuals.

Measures that survive a refactor

Base measures stay dumb: a sum, a count, a distinct count. Everything else composes them. When the definition of revenue changes — and it will — you change one measure, not forty.

If you cannot explain a measure in one sentence without saying "and then it also", it is doing two jobs and should be two measures.

Row-level security without the performance tax

RLS applied to a fact table re-evaluates on every visual. Applied to a small dimension that filters the fact, it costs almost nothing. The pattern is always the same: filter the smallest table that gets you there.

-- The security table: one row per user per region.
CREATE VIEW dbo.vw_user_region AS
SELECT
    u.email        AS user_email,
    r.region_key
FROM dbo.dim_user AS u
JOIN dbo.user_region_map AS m ON m.user_id = u.user_id
JOIN dbo.dim_region      AS r ON r.region_id = m.region_id;

-- In Power BI, the RLS filter goes on dim_region, never on fact_sales:
--   [region_key] IN
--     SELECTCOLUMNS(
--       FILTER(vw_user_region, [user_email] = USERPRINCIPALNAME()),
--       "region_key", [region_key]
--     )

Test it before you ship it

Every RLS role gets checked the same way, in this order:

1. View as role in Desktop, against a known user in each region.
2. Publish to a dev workspace and re-check — Desktop and Service do not always agree.
3. Have one real user from each region confirm their own totals.
4. Only then point the production dashboards at it.

Sigma: when the warehouse should be the model

Not everything belongs in a semantic model. When the analysis is exploratory, the data is enormous, and the audience already thinks in spreadsheets, Sigma's warehouse-native model is usually the cheaper answer — the logic stays in Snowflake, and there is no refresh to schedule because there is no import.

The trade-off is governance. A warehouse-native workbook is only as trustworthy as the tables underneath it, so dbt tests and ownership matter more, not less.

The handover checklist

Before we call an engagement done:

• Every measure has a description naming its owner.
• The model refreshes inside its window with 30% headroom.
• RLS is tested by a real user in every role.
• There is a written page explaining what each dashboard is for and what it is not for.
• Someone on the client's team has rebuilt one visual from scratch, unaided.

That last one is the real test. If nobody on your side can extend it, you did not buy a BI system — you rented one.