Building Power BI Reports from Dimensional Models - A Practical Walkthrough

If you've ever inherited an Excel workbook with sales data and been told "make it look good in Power BI," you know the anxiety. The data's there, the tool's there, but the gap between raw dimensional model and finished report feels bigger than it should.

It doesn't have to be. I've watched junior analysts at client sites go from blank canvas to polished executive report in under an hour once they understand the pattern. The trick isn't knowing every button in Power BI Desktop - it's understanding the shape of your data first, then letting that shape guide the visuals.

Microsoft's tutorial on building reports from dimensional models walks through this using the AdventureWorks dataset. I want to go beyond the tutorial and share what actually matters when you're doing this in a real Australian business context.

Start with the Star Schema

Most enterprise data ends up in a star schema, whether it comes from a proper data warehouse or an analyst who's structured their Excel sheets sensibly. You have a fact table in the middle - usually transactions or events - surrounded by dimension tables that give those transactions meaning.

In the AdventureWorks example, the fact table is Sales. The dimensions are Customer, Date, Product, Reseller, and Sales Territory. Each dimension answers a different question: who bought it, when, what was it, who sold it, and where.

Here's what I tell every client: before you touch a visual, spend ten minutes looking at your model view. Check the relationships. Are the lines connecting the right tables? Are the cardinalities correct? A one-to-many relationship going the wrong direction will give you wrong numbers in every single report, and you won't notice until someone senior points out the totals don't match.

Getting Relationships Right

The Date table is almost always where people trip up. In most models, the fact table has multiple date columns - order date, ship date, due date. But Power BI only allows one active relationship between two tables. The rest have to be inactive.

This means if you drag DateKey to OrderDateKey to create your primary relationship, then create relationships for DueDateKey and ShipDateKey as well, only the OrderDateKey one will be active by default. The other two show as dashed lines in the model view.

That's not a bug. It's a design choice, and it's actually the right call for most reports. Your default time axis should be order date. When you need to analyse by due date or ship date, you write a DAX measure that activates the inactive relationship using USERELATIONSHIP.

Sales Amount by Due Date =
CALCULATE(
    SUM(Sales[Sales Amount]),
    USERELATIONSHIP(Sales[DueDateKey], 'Date'[DateKey])
)

We've seen this pattern across every manufacturing and logistics client we've worked with through our Power BI consulting engagements. The finance team wants order date, the warehouse team wants ship date, and the operations team wants due date. One model serves all three - you just need the right measures.

Clean Up Before You Build

Two things I always do before creating any visuals:

Hide key columns. Every foreign key in your fact table - CustomerKey, ProductKey, ResellerKey - should be hidden from report view. Nobody's going to drag a key column onto a chart. All it does is clutter the fields list and confuse business users.

Right-click the column, choose "Hide in report view." Do this for every column that ends in "Key" across every table. It takes two minutes and saves hours of confusion later.

Create hierarchies. This is where Power BI starts feeling properly useful. Geography hierarchies (Country > State > City > Postal Code > Customer) let users drill down through a map or table without needing to know which fields to pick. Product hierarchies (Category > Subcategory > Model > Product) do the same for product analysis.

Fiscal hierarchies on the Date table are particularly valuable. Year > Quarter > Month > Date gives users a natural way to zoom in and out of time-based analysis. I've seen clients who skip this step and then wonder why their reports feel clunky compared to what they've seen in demos. The hierarchies are the difference.

Building the Actual Report

With a clean model, the visuals almost build themselves. Here's the approach I use for executive summary reports, which is the most common request we get.

Title and context first. Insert a text box, type something descriptive, and format it prominently. "Executive Summary - Sales Report" tells the reader exactly what they're looking at. I like adding a coloured background rectangle behind the title - it separates the header from the data visually.

Area chart for trends. Drag Sales Amount to a blank area, then add Month from the Fiscal hierarchy. Remove Year and Quarter from the X-axis so you get a smooth month-by-month flow. Change the chart type from column to area. If you've written the Due Date measure, add it to the Y-axis as well - you'll see how due date amounts trail slightly behind order date amounts, which makes intuitive sense.

Map for geographic context. Drag Country-Region from the Reseller table, then add Order Quantity as the bubble size. Instantly, you can see which markets are performing. For Australian companies with international distribution, this is usually the visual that gets the most attention in boardroom presentations.

Matrix for the detail. A matrix with Product Category on rows, Business Type on rows beneath it, and Sales Amount as the value gives you a detailed breakdown that users can scan quickly. Add conditional formatting with data bars and it becomes immediately obvious which combinations are driving revenue.

Slicer for interactivity. A Fiscal hierarchy slicer on the left lets users filter everything on the page to a specific year, quarter, or month. This single addition turns a static report into an interactive tool.

Formatting That Actually Helps

Most reports I see in the wild are either completely unformatted or over-formatted. The sweet spot is simpler than you'd think.

Switch to the Executive theme (View ribbon > Themes > Executive). It gives you a professional colour palette without you needing to pick individual colours. Then add shadows to your visuals - it creates subtle depth that makes charts feel like objects on the page rather than flat rectangles.

Set all visual titles to 16pt. Make them descriptive enough that someone can understand the chart without looking at the axes. "Sales Amount by Order Date" is better than "Chart 1."

One thing I'd add that the Microsoft tutorial doesn't emphasise enough: whitespace. Leave gaps between visuals. Don't try to fill every pixel. Reports that breathe are easier to read, and easier to present.

Publishing and Sharing

Once your report is done in Power BI Desktop, you publish it to the Power BI Service. This is where it becomes a team resource rather than a file on your laptop.

From the Home ribbon, click Publish, choose your workspace, and you're done. Colleagues with Power BI accounts can view and interact with the report - filtering, drilling down, bookmarking views - but they can't modify your model or visuals.

For organisations using Microsoft Fabric or considering it, the publishing workflow fits naturally into the broader data estate. Reports built in Desktop connect to datasets in the service, which can connect to lakehouses and warehouses in Fabric. If you're exploring that path, our Microsoft Fabric consulting team can help you plan the architecture so your reports scale properly.

Common Mistakes We See

Not setting data types in Power Query. If a column that should be a Whole Number is imported as text, your relationships won't work properly and your DAX calculations will be slow or wrong. Always check data types before loading.

Too many visuals on one page. Five or six is the maximum for an executive summary. If you need more detail, create additional pages. Tab navigation exists for a reason.

Ignoring the model view. I've debugged reports where the numbers were wrong because someone had an accidental many-to-many relationship. Five minutes in model view would have caught it before anyone built a single chart.

Using default column names. Rename "SalesTerritory" to "Sales Territory" and "SalesOrder" to "Sales Order." Small thing, but it makes the fields list readable. Excel table names can't have spaces, so the import preserves the squished names. Fix them in Power BI.

Where This Fits for Your Business

Building a report from a dimensional model is one of those skills that pays off immediately. Every department has some version of "I need to see the numbers" - and most of the time, the data already exists in a shape that's close to usable.

The gap isn't usually the tool. It's understanding the data model well enough to build the right visuals. If your team needs help getting from raw data to dashboards that executives actually use, our business intelligence solutions work covers exactly this kind of engagement - from data modelling through to report design and user training.

The Microsoft tutorial gives you the mechanics. What I've tried to add here is the judgement calls - which steps matter most, where people get stuck, and what makes the difference between a report that gets opened once and one that becomes a daily tool.