Home/Blog/Coding & Data
Coding & Data · 11 min read

How to build a Power BI dashboard people can trust

The short answer

To build a Power BI dashboard people can trust, agree the questions and decisions first, clean data in named Power Query steps and reconcile totals with the source, model a star schema with a marked date table, test every DAX measure against a value worked out another way, then check accessibility, row-level security and refresh before you publish through an app.

Want the tools, not just the method? The The Power BI Dashboard Builder Kit kit turns this guide into ready-to-use templates, spreadsheets and printables, with an instant download.

Key takeaways
  • Write the questions and the decision each supports before you open Power BI Desktop.
  • Count rows after every Power Query step and reconcile the final total with the source owner.
  • Use a star schema: dimensions filter, facts are summarised, relationships run one-to-many.
  • Test each measure against an expected value from a different method; no PASS, no publish.
  • Row-level security filters only workspace Viewers, so readers get the app or the Viewer role.
  • Treat Copilot output as a draft and test it like any other measure.

What are the steps to build a Power BI dashboard people can trust?

Nine steps cover the whole job. They work whichever licence you have and whatever your data source, because they are about how you build and check rather than where a button sits this month. The order matters: testing sits in the middle because nothing after it is worth doing if the numbers are wrong.

  • 1. Write a one-page brief: the questions in the user's words, the decision each supports, the grain, the users, the data owners, refresh and security.
  • 2. Load raw data into Power Query and clean it in named steps; count rows after each step.
  • 3. Reconcile the clean total with the source system's own figure and get the data owner to confirm it.
  • 4. Model a star schema: a fact table of events, dimension tables of things, one-to-many relationships in one direction.
  • 5. Add a date table with full years that covers every related date column, and mark it as a date table.
  • 6. Write small explicit measures, reuse them, and use DIVIDE for every ratio.
  • 7. Test every measure against an expected value worked out another way, and log PASS or FAIL.
  • 8. Build pages that answer the brief and pass accessibility checks, then set access with apps, Viewer roles and row-level security.
  • 9. Plan refresh with a named gateway owner, ask a second person to run a QA checklist, and publish only when a readiness rule says go.

Why do Power BI reports and Excel packs disagree?

Usually because one of them is summing rows that should not be there. Exports from business systems often repeat lines, include test transactions or carry text where numbers should be. Spreadsheet research has found errors common for decades: one review of fifteen years of studies found them common and non-trivial, with careful cell-by-cell inspection the method shown to work. A Power BI model does not remove that risk; it moves it into query steps, relationships and measures, where it is easier to test if you plan for testing.

The fix is not to adjust either number until they match. Count rows after every Power Query step, find the step where they change, and explain the change. In the worked example below, the old pack overstated one month by about 1.4%, and the explanation took under two hours.

What does a reconciliation look like in practice?

Here is the worked example from the practice data: a fictional 700-person manufacturer's October export of 610 rows. After trimming text, removing 2 test orders and one empty order shell, typing values with the right culture and removing duplicates, 600 rows remain. The table shows how the old spreadsheet total and the clean total relate.

Every line is something you can show a finance director: a count, an amount and a reason. Once the data owner confirms the source total, the reconciliation goes into an evidence register, and the same Power Query steps run unchanged next month.

StepRowsAmount (USD)
Raw export, summed as-is (old pack)610$2,400,820.78
Remove 7 duplicated lines-7-$31,605.27
Remove 2 test orders and 1 empty shell-3-$924.32
Clean October total, confirmed by the source owner600$2,368,291.19
Difference the old pack carried$32,529.59

Why use a star schema in Power BI?

Because Power BI's engine is built for it. Microsoft's guidance puts it simply: dimension tables enable filtering and grouping, fact tables enable summarisation, and the one side of a relationship is always a dimension. The idea goes back to the relational model, in which people query related tables without needing to know how the data is stored.

In practice that means one row per event in the fact table (an order line, a payment), one row per thing in each dimension (a product, a customer, a date), a single unique key in every dimension, and one-to-many relationships that filter in one direction. Flatten product, subcategory and category into one table. Hide keys and any column that should never be summed, such as a unit price, and show numbers through measures.

Dates need their own table: unique dates with no gaps and full years, marked as a date table. Make it cover every date column that relates to it. A common slip is ending the table at the last order date, so late-December orders that ship in January vanish from ship-date measures.

How do you test DAX measures?

Pick the contexts that matter (a full year, a month, a category, a region, a date at the end of a period), work out the expected value by a different method, run the measure in the same context and compare within a tolerance. A query on the source, a pivot table on the raw export or a short script all count as a different method; the same DAX on another page does not.

UK government guidance on quality analysis calls this verification and expects the person assuring the work to be independent of the person who built it. In Power BI, DAX query view lets you run a set of test queries in one go: a UNION of ROW expressions, one per test, each using CALCULATE with the test's filters. Log the expected value, the actual value, the difference and PASS or FAIL, and re-run the whole set after every change.

The test that catches the most common bug is year-over-year percentage. It should divide the change by last year's sales. In the worked example, a draft that divided by this year's sales returned 7.71% instead of 8.35%: plausible, and wrong.

Who does row-level security apply to in Power BI?

Row-level security (RLS) restricts data only for people in the Viewer role of a workspace; Admins, Members and Contributors are not filtered by it. That single rule explains most RLS surprises: a regional manager added as a Member sees every region, even though the role tested perfectly.

Define roles and their DAX rules in Power BI Desktop, test them with View as there and Test as role in the service, assign security groups in the service, and give readers access through an app or the Viewer role. Keep an access register with each person's workspace role, RLS role and whether they must be filtered, and flag anyone who must be filtered but holds a higher role. Roles add up, and RLS filters rows rather than columns, so a person who can see a row sees all of it.

Measure where you are

Reading helps; measuring tells you what to work on. These AI-graded assessments on AssessAll pair with this topic:

Can Copilot in Power BI write my measures?

It can draft them, if your organisation has the capacity Copilot needs: Microsoft documents paid Fabric capacity of F2 or higher or Premium P1 or higher, a tenant setting and a supported region, and warns that without data preparation Copilot can return generic or inaccurate results. Treat every output as a draft.

Independent benchmarks explain why. On BIRD, a large text-to-SQL benchmark, the best model tested in 2023 reached 40.08% execution accuracy against 92.96% for people. On Spider 2.0, a 2025 benchmark of real enterprise workflows, the best reported agent solved 21.3% of tasks. These are SQL tasks rather than DAX, and models keep improving, but realistic business data is clearly harder than tidy examples. Draft, run the test, keep only after a PASS.

How do you make a Power BI report accessible?

Start with the WCAG 2.2 basics: text contrast of at least 4.5:1, graphical elements at least 3:1, and colour never the only way of carrying meaning. Red-green colour vision deficiency affects about 1 in 12 men of Northern European descent, so back colour with labels, markers or patterns.

Screen-reader users face the largest gap: in one study they extracted information from online charts 61.48% less accurately than other participants. In Power BI, write alt text that states each visual's insight, set the tab order so it follows reading order, hide decorative shapes from it, turn on markers for line charts and check the Show data table. Then make a mobile layout for the page people open on their phones.

How should you set up refresh and gateways?

A report in import mode shows a copy of the data taken at the last refresh, so plan refresh before you publish, not after the first complaint. If the Power BI service cannot reach a source directly, as with a database on your own network, it needs a gateway installed on a server inside that network; Microsoft recommends a gateway managed by administrators, with personal mode only for one person's own models.

Microsoft documents up to 8 scheduled refreshes a day on shared capacity and up to 48 on Premium, PPU or Fabric capacity, a two-hour limit for a refresh on shared capacity, and a pause after two months with no visits. Schedule after the source finishes its overnight jobs and away from the gateway server's patching window, store credentials in a connection owned by a service account or a named owner, and send failure emails to at least two people. Keep a refresh log for the first two weeks: three completed refreshes in a row is a sensible condition for going live.

What are the most common mistakes when building Power BI dashboards?

Most failures are small decisions made for speed. Each one has a simple guard.

  • Copying the old report instead of asking which decisions it supports: write the brief first.
  • Fixing data in Excel before loading: clean in named Power Query steps so the work repeats.
  • Profiling only the first 1,000 rows: switch profiling to the entire data set before reading counts.
  • One wide table from an export: split it into a fact table and dimensions.
  • Bidirectional relationships to make a slicer work: filter from dimensions, or use CROSSFILTER inside one measure.
  • Testing a measure against itself: use an independent method for expected values.
  • Adding readers as workspace Members: they bypass RLS; use the app or the Viewer role.
  • Refresh on one person's credentials: use a gateway connection with a named owner and two failure contacts.
  • Publishing without a second pair of eyes: a colleague runs the QA checklist, and a readiness rule decides.
Bodhih Pro Kit

Build your next report with the files for every step

The Power BI Dashboard Builder Kit gives you practice data with expected results, 30 tested measures, the test log, access register, QA checklist and readiness check in one download. Not affiliated with or endorsed by Microsoft. Microsoft, Power BI, Power Query, DAX, Microsoft Fabric, Copilot and Excel are named descriptively only.

Common questions

Questions people ask next

Is Power BI Desktop free?

Yes. Power BI Desktop is a free Windows download for building models and reports. Sharing in the Power BI service needs a Pro or Premium Per User licence, and viewers need one too unless the workspace is on Fabric capacity of F64 or larger.

How many refreshes a day can Power BI run?

Microsoft documents up to 8 scheduled refreshes a day on shared capacity and up to 48 on Premium, PPU or Fabric capacity, and pauses scheduled refresh after two months without visits. Check the current limits before you plan.

Do I need a date table if Power BI creates one automatically?

For anything beyond quick exploration, yes. Microsoft advises keeping Auto date/time only for simple calendar needs or ad hoc work, and lists the requirements for a proper date table: unique dates, no blanks, no gaps and full years.

Why does my CALCULATE measure show the same value in every row?

A filter argument on a column that is already filtered replaces the existing filter, so every row shows the filtered value. Wrap the filter in KEEPFILTERS if you want it to intersect with the row's filter instead.

What is the difference between a measure and a calculated column?

A measure is evaluated when a visual asks for it, in that cell's filter context; a calculated column is stored row by row at refresh. Use measures for numbers shown in visuals and for ratios, so totals recalculate rather than add up.

How do I check my Power BI skills?

Practise with a dataset that has known answers and compare your results. For an independent measure, AssessAll's Business Intelligence Dashboard Design Work Sample tests the build skills this guide describes.

How do I plan time to practise Power BI?

Book short sessions on working days for a month and set goals you can keep; the WOOP method on Jobulary is a simple way to plan them and the obstacles that get in the way.