Michael Tall — Header
Hands defining data grain with notes on desk

Data modelling in Power BI: a practical guide for report builders

Data modelling in Power BI is the organised design of fact and dimension tables — structured as a star schema — to make reports accurate, fast, and maintainable. Microsoft Learn’s star-schema guidance establishes this as the default pattern for Power BI: narrow fact tables connected to descriptive dimension tables via one-to-many, single-direction relationships. Get this foundation right and almost every other modelling decision becomes easier.

Three actions you can apply immediately:

  • Use a single fact table per subject area. Mixing granularities in one fact table is the most common cause of incorrect aggregations.
  • Add a proper date table. Disable auto date/time in Power BI Desktop settings and replace it with a contiguous, purpose-built calendar table. Time-intelligence measures depend on it.
  • Create explicit measures and hide technical keys. Implicit aggregations (dragging a column into a visual) bypass your business logic. Explicit measures enforce it.

These three steps alone will measurably improve the accuracy and performance of most models in production today.


Key takeaways

A well-designed Power BI semantic model starts with a clearly defined grain, a proper date table, and explicit measures — everything else builds on those three foundations.

Point Details
Star schema is the default Use one fact table per subject area with dimension tables connected via one-to-many, single-direction relationships.
Grain must be defined first Write down what one row represents before building any relationship; mixed grains cause incorrect aggregations.
Explicit measures over implicit Write named DAX measures for every business calculation; hide numeric columns that should never be summed directly.
Storage mode drives architecture Use Import for most models; add Dual-mode dimensions and Direct Lake for large-scale Fabric solutions.
Datataal for implementation Datataal provides 3x certified Microsoft Fabric consulting for semantic model design, migration, and performance optimisation.

Table of Contents

What are the core concepts behind Power BI data modelling?

Grain: the most important decision you will make

Grain defines what a single row in a fact table represents. A sales fact table might be at order-line grain (one row per product per order) or at order grain (one row per order). Choosing the wrong grain, or mixing grains within a table, produces aggregations that are mathematically incorrect. Before writing a single relationship, write down the grain in plain English and confirm every column in the fact table belongs at that level.

Fact tables versus dimension tables

Dimension tables answer who, what, where, when, and why. They are wide (many descriptive columns), relatively short (fewer rows), and drive filtering and grouping in your visuals. Fact tables answer how much and how many. They are narrow (mostly keys and numeric measures), often very long, and exist to be summarised. Microsoft’s star-schema documentation describes this split precisely: dimensions filter, facts aggregate.

Star schema versus snowflake

A star schema denormalises dimension data into a single table per dimension. A snowflake normalises dimensions further, splitting, for example, a Product dimension into Product, Category, and Subcategory tables. In Power BI, the snowflake pattern adds joins, increases query complexity, and rarely improves performance. Denormalise your dimensions unless the source system makes it genuinely impractical. The MSSQLTips star-schema guide confirms that the star schema reduces joins and simplifies queries compared with highly normalised designs.

Explicit measures versus implicit aggregations

An implicit aggregation is what Power BI creates automatically when you drag a numeric column into a visual. It looks fine in a simple report. It breaks the moment a user applies a filter you did not anticipate, or when a colleague reuses the column in a different context. Explicit measures, written in DAX and stored in the model, apply consistent business logic regardless of visual context.

Pro Tip: Mark every numeric column that should never be summed directly (such as a unit price or a percentage) as non-summarisable in the column properties. This forces report authors to use your explicit measures instead.


How do you design a clean star schema in Power BI?

Choosing and verifying your fact table grain

Start by listing the source columns you intend to load. Group them: which columns identify the event (order ID, date, customer, product), and which measure it (quantity, revenue, cost)? The identifier columns define the grain. Verify it by checking for duplicate combinations of those identifier columns. If duplicates exist, your grain is wrong or your source data needs further transformation in Power Query before it reaches the model.

A common incorrect grain: loading a header-level sales table and joining it to a line-level cost table. The join produces a many-to-many relationship and inflated totals. Fix it upstream, not in DAX.

Surrogate keys and data types

Use surrogate keys (integer identifiers generated in your ETL or Power Query) rather than natural keys (product codes, customer numbers) for relationships. Microsoft’s modelling guidelines are explicit: use Int64 for key columns and avoid Double (floating-point) types for critical numeric data. Integer keys compress better in the VertiPaq engine, load faster, and join without type-mismatch errors.

Steps for clean key management:

  1. Generate a surrogate integer key in Power Query or upstream SQL for each dimension.
  2. Replace natural keys in the fact table with the corresponding surrogate keys.
  3. Hide all key columns from report view once relationships are established.
  4. Never expose a surrogate key as a user-facing field.

Role-playing dimensions

A role-playing dimension is a single dimension table used multiple times in the same model, each time in a different role. Dates are the classic example: an order might have an Order Date, a Ship Date, and a Due Date, all pointing to the same calendar table. In Power BI, you handle this by creating multiple inactive relationships and activating the correct one inside a DAX measure using USERELATIONSHIP. Alternatively, create separate date dimension views in Power Query (Order Date, Ship Date, Due Date) that each reference the same underlying calendar query. The second approach is simpler for report authors because the active relationship is always obvious.

Date table requirements

Your date table must:

  • Cover a contiguous range from the earliest date in your fact data to the latest (with a buffer of at least one year forward for forecasting).
  • Contain a Date column of type Date (not DateTime).
  • Be marked as a date table in Power BI Desktop (right-click the table in the Fields pane).
  • Include at minimum: Year, Quarter, Month Number, Month Name, Week Number, Day of Week, and a fiscal period if your organisation uses one.

Disable auto date/time in File > Options > Data Load. Auto date/time creates hidden date hierarchies for every DateTime column in the model, inflating memory usage with no benefit once a proper date table exists.


How do relationships affect filter propagation and query accuracy?

Cardinality options and practical rules

Power BI supports three cardinality types: one-to-many, many-to-many, and one-to-one. One-to-many is the correct choice for nearly every fact-to-dimension relationship. Many-to-many relationships are sometimes unavoidable (shared dimensions across fact tables at different grains), but they carry a performance cost and require careful validation. One-to-one relationships are rare; they usually signal that two tables should be merged.

A practical rule: if you cannot identify which table holds the “one” side (unique values) and which holds the “many” side (repeated values), your grain definition needs revisiting before you set the relationship.

Cross-filter direction

Single-direction filtering is the default and the correct choice for most relationships. Filters flow from the dimension table to the fact table. Bi-directional filtering allows filters to flow both ways, which sounds useful but creates ambiguity in complex models, can cause circular dependency errors, and degrades query performance. Use bi-directional filtering only when you have a specific, tested reason — for example, a bridge table in a many-to-many scenario.

Pro Tip: If you need a filter to cross from a fact table back to a dimension, write a DAX measure using CROSSFILTER or TREATAS instead of enabling bi-directional filtering on the relationship itself. You get the same result with explicit, auditable logic.

Inactive relationships and USERELATIONSHIP

Inactive relationships exist in the model but do not propagate filters by default. They become active only inside a DAX measure that calls USERELATIONSHIP. This is the correct pattern for role-playing dimensions and for scenarios where you need to switch between two fact-to-dimension paths depending on the measure being calculated. Document every inactive relationship in the table description so future model authors understand why it exists.

Common pitfalls to avoid:

  • Mismatched data types on relationship columns (a Text key joined to an Integer key silently fails or degrades performance).
  • Duplicate values on the “one” side of a one-to-many relationship (Power BI will downgrade the relationship to many-to-many without warning).
  • Relationships built on high-cardinality string columns (GUIDs, email addresses) instead of integer surrogate keys.

Which storage mode should you choose for your Power BI model?

Storage mode is one of the most consequential architectural decisions in Power BI data model design. The wrong choice produces either stale data or unacceptably slow queries.

Mode Data location Refresh needed Best for
Import In-memory (VertiPaq) Scheduled or on-demand Most models; fast queries, full DAX support
DirectQuery Source system None (live query) Large datasets where freshness is critical
Dual Both (context-dependent) Scheduled for in-memory copy Dimension tables in composite models

Microsoft’s composite model guidance describes Dual storage as the recommended mode for dimension tables in hybrid architectures: the engine uses the in-memory copy when querying against an Import fact table, and the DirectQuery copy when querying against a DirectQuery or Direct Lake fact table.

Import versus DirectQuery

Import mode loads data into VertiPaq, the in-memory columnar engine. Queries are fast, full DAX time-intelligence works, and the model is self-contained. The trade-off is refresh latency: data is only as current as the last refresh.

DirectQuery sends every query to the source system in real time. Data is always current, but query speed depends entirely on the source system’s performance. Many DAX functions, particularly time-intelligence functions, do not work in pure DirectQuery mode. Use DirectQuery when data freshness requirements cannot be met by scheduled refresh, and when the source system can handle the query load.

Composite models and Microsoft Fabric

Composite models combine Import, DirectQuery, and Dual tables in a single semantic model. In Microsoft Fabric, Direct Lake mode adds a third option: the engine reads Parquet files directly from OneLake without a traditional import refresh, combining near-real-time freshness with in-memory query speed. For large-scale solutions, Fabric’s Direct Lake pattern is the architecture to target.

For large datasets in Import mode, use incremental refresh policies (configured in Power Query using the RangeStart and RangeEnd parameters) to partition fact tables by date. This approach reduces refresh duration and source load significantly.


How should you write measures and calculated columns in your model?

Explicit measures are the primary calculation object in a well-designed Power BI semantic model. Calculated columns are the exception, not the rule.

When to use calculated columns

A calculated column is evaluated row by row during refresh and stored in the model, consuming memory. Use one only when:

  • You need a column for slicing or filtering (not just for a measure result).
  • The calculation cannot be expressed as a measure because it requires row context that does not exist at query time.
  • The computation is genuinely simpler in the model than in Power Query or upstream SQL.

In practice, most calculations that developers put in calculated columns belong in Power Query (as a transformation step) or in the source query. Compute upstream wherever possible. The Power BI best-practice rules are explicit: minimise calculated columns and prefer measures.

Naming, organisation, and documentation

  • Group related measures into display folders (set the Display Folder property in the Properties pane or in Tabular Editor).
  • Write a description for every measure. Future you, and every colleague who inherits the model, will thank you.
  • Apply a consistent format string (e.g. #,##0 for integers, £#,##0.00 for currency, 0.00% for percentages).
  • Prefix measures with a category if the model is large: Sales [Total Revenue], Sales [Gross Margin %].

DAX anti-patterns to avoid

  • Heavy calculated columns: a calculated column that calls RELATED repeatedly across a large fact table inflates memory and slows refresh. Move that logic to Power Query.
  • Excessive RELATED usage in measures: restructure the model so the dimension attribute is available via the relationship rather than pulled into the fact table.
  • Implicit measures left in the model: hide or remove every numeric column that has no business meaning as a raw sum. If OrderID is an integer, mark it as non-summarisable.

How do you optimise a Power BI model for performance and memory?

Performance optimisation in Power BI data modelling is largely about reducing what the VertiPaq engine has to store and scan. Smaller models query faster and refresh more reliably.

Reducing model size

The DataCamp Power BI modelling tutorial identifies three high-impact steps: remove unused columns, load only the historical rows users actually need, and disable auto date/time. To these, add:

  • Split DateTime columns into separate Date and Time columns. VertiPaq compresses Date columns far more efficiently than DateTime.
  • Replace high-cardinality string columns (free-text descriptions, GUIDs) with integer codes where possible.
  • Remove or hide columns that exist only to support a relationship key once the relationship is established.

Pro Tip: Before publishing, open Performance Analyzer in Power BI Desktop and run every report page. Any visual taking more than two seconds to render has a query worth investigating in DAX Studio.

Incremental refresh and partitioning

For fact tables with millions of rows, configure incremental refresh using Power Query’s RangeStart and RangeEnd date parameters. Define a rolling window (for example, refresh the last 30 days, keep three years of history). Power BI creates partitions automatically and refreshes only the partitions that fall within the rolling window. This reduces refresh time from hours to minutes for most large fact tables.

Optimisation Effort Impact
Remove unused columns Low High
Disable auto date/time Low Medium–High
Split DateTime columns Low Medium
Incremental refresh Medium High
Aggregation tables High Very high

Aggregation tables

For very large fact tables (hundreds of millions of rows), pre-aggregated summary tables let Power BI answer most queries from a compact, fast table rather than scanning the full detail. The engine falls back to the detail table only when a query requires granularity the aggregation cannot satisfy. This pattern requires careful design but can reduce query times by an order of magnitude for summary-level reporting.


Which tools do professional model builders use to validate their work?

Model view and Model explorer

Model view in Power BI Desktop is the primary visual surface for managing relationships, setting properties, and organising complex models. Use separate diagram layouts to create focused views of specific subject areas rather than one overwhelming diagram of the entire model. The Model explorer pane (in preview as of recent Desktop releases) lets you inspect and edit object properties without switching between panes.

Tabular Editor and Best Practice Analyzer

Tabular Editor’s Best Practice Analyzer scans your model against a curated set of rules and flags violations. The Power BI team’s published rule set covers naming conventions, hidden columns, calculated column usage, auto-date settings, and more. Best Practice Analyzer can generate fix scripts for many common issues, turning a manual audit into a near-automated process. Think of it as a spell-checker for your semantic model.

Tabular Editor also gives you a scripting environment (C#) for bulk operations: renaming objects, setting display folders across dozens of measures, or applying format strings consistently. Operations that would take an hour in Power BI Desktop take minutes in Tabular Editor.

DAX Studio

DAX Studio connects to your Power BI Desktop file or published dataset and lets you run DAX queries directly, inspect query plans, and profile server timings. Use it to:

  • Identify which measures trigger the most expensive storage engine scans.
  • Compare query plans before and after a model change.
  • Validate that a measure returns the correct result for a specific filter context.
  • Step 1: Shape and clean data in Power Query. Remove unused columns here, not in the model.
  • Step 2: Design relationships and set properties in Model view.
  • Step 3: Write explicit measures; organise into display folders.
  • Step 4: Run Tabular Editor’s Best Practice Analyzer and resolve flagged issues.
  • Step 5: Profile slow visuals with DAX Studio and optimise the underlying measures or model structure.
  • Step 6: Publish to a development workspace; validate against source data before promoting to production.

How do you test, validate, and govern a Power BI semantic model?

A model that produces wrong numbers is worse than no model at all. Validation and governance are not optional extras; they are part of the build.

Data validation checks

Before publishing, run these checks:

  • Row count reconciliation: total rows in each fact table should match the source system for the same date range.
  • Key uniqueness: dimension tables must have no duplicate values on the key column used in relationships.
  • Null key check: fact table foreign keys must have no nulls (or a deliberate “unknown” dimension row to catch them).
  • Measure spot-checks: calculate three to five key measures manually from the source and compare against the model output.

Pro Tip: Create a hidden “Validation” page in your report with row count cards, null key counts, and key measure comparisons against a known-good source. Leave it in the file for every future refresh cycle.

Documenting the model

Write a description for every table, every measure, and every non-obvious column. Power BI surfaces these descriptions in the Fields pane tooltip. At minimum, document:

  • The grain of each fact table (in the table description).
  • The intended use and business definition of each measure.
  • Any inactive relationships and why they exist.

Row-Level Security

Row-Level Security (RLS) restricts which rows a user sees based on their identity. Define RLS roles in Power BI Desktop (Modelling tab > Manage Roles), write DAX filter expressions for each role, and test using “View as role” before publishing. For dynamic RLS (where the filter depends on the logged-in user), use USERPRINCIPALNAME() in the DAX filter expression joined to a security mapping table.

Separate development, test, and production workspaces. Promote models through these stages using deployment pipelines in the Power BI service. This prevents untested changes from reaching end users.

Version control basics

Store your .pbix or .bim file in a Git repository. Tabular Editor’s “Save to folder” format serialises the model as individual JSON files per object, making diffs readable and merge conflicts resolvable. Connect the repository to Azure DevOps or GitHub and require pull-request review before merging model changes to the main branch.


A step-by-step checklist to build an optimised Power BI semantic model

Follow these steps in order. Skipping ahead typically creates rework.

  1. Define the grain (15–30 minutes for a small model; 1–2 hours for a large one). Write it down. Confirm with the business owner.
  2. Prepare data in Power Query (1–4 hours). Remove unused columns, apply data types, generate surrogate keys, and create the date table query.
  3. Create and mark the date table (30 minutes). Verify contiguous range, mark as date table, disable auto date/time.
  4. Establish relationships in Model view (30–60 minutes). Set cardinality and cross-filter direction for each. Verify no unexpected many-to-many relationships.
  5. Create explicit measures (1–3 hours depending on complexity). Organise into display folders, apply format strings, write descriptions.
  6. Run Tabular Editor Best Practice Analyzer (15–30 minutes). Resolve all high-severity findings before proceeding.
  7. Profile with DAX Studio (30–60 minutes). Run Performance Analyzer on report pages; investigate any measure taking over one second.
  8. Validate against source data (30–60 minutes). Row counts, key uniqueness, measure spot-checks.
  9. Publish to development workspace and test RLS (30 minutes).
  10. Promote to production via deployment pipeline once sign-off is received.

For a small model, this entire process typically can be completed within a few days. Larger models with complex composite architecture and incremental refresh may require longer to implement.

Quick remedial actions for common issues:

  • Slow visuals: check for bi-directional relationships and disable where not needed; profile in DAX Studio.
  • Incorrect totals: check for mixed grains in the fact table; verify measure filter context with CALCULATE.
  • Refresh failures: check Power Query step errors; verify incremental refresh parameters are correctly typed.
  • Memory warnings: run Best Practice Analyzer; remove unused columns and disable auto date/time.

Lessons from real Microsoft Fabric consulting projects

Most organisations that come to me with a Power BI performance problem have the same root cause: the model was built to answer one specific question, then grew organically as new requirements arrived. Columns were added without removing old ones. Calculated columns replaced what should have been Power Query steps. Relationships were added without checking cardinality. The result is a model that is technically functional but slow, hard to maintain, and trusted by nobody.

The approach I use at Datataal starts with grain. Before touching Power BI Desktop, I document what each fact table represents, confirm it with the data owner, and only then begin building. In Microsoft Fabric projects, this extends to OneLake architecture: which data lands in a Lakehouse, which is surfaced via Direct Lake, and which dimensions use Dual storage mode for composite model performance.

The outcomes are consistent: faster refresh cycles, simpler DAX measures, and report authors who can extend the model themselves without breaking it.


Lessons from real Microsoft Fabric consulting projects — overview diagram

How do you version-control and collaborate on Power BI data models?

Version control for Power BI models has historically been difficult because .pbix files are binary. The practical solution is Tabular Editor’s “Save to folder” feature, which writes each model object (table, measure, column, relationship) as a separate JSON file. These files are human-readable, diff-friendly, and merge-compatible in Git.

A workable collaboration workflow:

  • Each developer works on a feature branch.
  • Changes are reviewed via pull request, with a checklist that includes Best Practice Analyzer results and a brief description of what changed and why.
  • The main branch deploys to a shared development workspace via a CI/CD pipeline (Azure DevOps or GitHub Actions with the Tabular Editor CLI).
  • Promotion to production uses Power BI deployment pipelines, with a manual approval gate.

For teams using Microsoft Fabric, the Fabric Git integration feature allows workspace items (including semantic models) to be connected directly to a Git repository, making the sync between the service and source control more straightforward than the manual .bim export approach.


What are the most common pitfalls in Power BI data modelling?

Many-to-many relationships created by accident

This is the single most frequent structural error. It happens when a dimension table contains duplicate values on its key column, usually because the source data was not deduplicated before loading. Power BI silently downgrades the relationship from one-to-many to many-to-many. Totals become inflated and filter propagation behaves unexpectedly. Fix: add a deduplication step in Power Query before the dimension table loads.

Mixed granularity in a fact table

Loading order headers and order lines into the same table, then trying to calculate both header-level and line-level metrics, produces incorrect results for at least one of the two. Separate them into two fact tables at their respective grains and connect both to the shared dimensions.

Overuse of calculated columns

Calculated columns that call RELATED across millions of fact rows consume significant memory and slow refresh. The calculation belongs in Power Query or the source query, not the model. If you find yourself writing RELATED in a calculated column on a fact table, move the logic upstream.

Ignoring the date table

Relying on auto date/time hierarchies for time-intelligence calculations is convenient but fragile. Auto date/time creates a hidden date table per DateTime column, multiplying memory usage. A single, shared, properly marked date table is more efficient and gives you full control over fiscal periods, week numbering, and custom hierarchies.

Bi-directional relationships left on by default

Some developers enable bi-directional filtering on every relationship “just in case.” This creates ambiguous filter paths in models with more than a handful of tables and can cause measures to return incorrect results. Audit every bi-directional relationship and disable it unless there is a specific, documented reason for it.


How do calculated tables support advanced modelling scenarios?

A calculated table is a DAX-defined table that exists in the model but is not loaded from a data source. It is evaluated at refresh time and stored in memory like any other table. Used judiciously, calculated tables solve specific modelling problems that cannot be addressed any other way.

Common legitimate uses

Disconnected parameter tables. A calculated table containing a list of values (discount rates, scenario names, exchange rates) that a user selects via a slicer, with a measure that reads the selected value using SELECTEDVALUE. This pattern enables what-if analysis without any changes to the underlying data.

Date tables generated in DAX. CALENDAR(DATE(2020,1,1), DATE(2030,12,31)) creates a date table entirely in DAX. This is acceptable for smaller models, though a Power Query-generated date table is easier to extend with custom columns.

Bridge tables for many-to-many relationships. When two fact tables share a dimension at different grains, a calculated bridge table can resolve the relationship without duplicating dimension data.

Union tables. UNION(Table1, Table2) combines two tables with identical schemas into one. Useful when source data arrives in separate tables (monthly files, for example) and consolidation upstream is not possible.

When not to use calculated tables

Calculated tables that replicate large portions of fact data, or that perform heavy aggregations, inflate model size and slow refresh. If the calculation can be done in Power Query or the source query, do it there. Calculated tables are a modelling tool, not a substitute for proper ETL.


Where should you invest time first when improving a model?

Most teams have limited time for model improvement. The question is not what to fix, but what to fix first.

Grain definition and the date table produce the largest return per hour invested. A model with an ambiguous grain will generate incorrect numbers regardless of how well everything else is built. A model without a proper date table will produce broken time-intelligence measures that erode trust in the entire report suite. Fix these two things before anything else.

Explicit measures come next. Converting implicit aggregations to named, described, formatted measures is the single change that most improves long-term maintainability. It also makes the model auditable: every calculation has a name, a description, and a clear owner.

After those three, invest in automation. Tabular Editor’s Best Practice Analyzer running on every pull request catches regressions before they reach production. The upfront cost of configuring the rules is an hour or two. The ongoing benefit is a model that stays healthy without manual audits.

The trade-off worth acknowledging: a well-governed model with documentation, version control, and automated checks takes longer to build than a quick .pbix assembled under deadline pressure. The labour cost is real. So is the cost of maintaining an undocumented model that only one person understands. In my experience, the governed model pays back its extra build time within the first quarter of production use.


Where should you invest time first when improving a model? — overview diagram

How Datataal can help with your Power BI and Microsoft Fabric modelling

If your team is spending more time fixing model errors than building new reports, a structured engagement often resolves the root cause faster than iterative self-correction.

Datataal

Datataal offers Microsoft Fabric consulting services covering semantic model design, star-schema architecture, composite model implementation, and Power BI report optimisation. Whether you need a full model rebuild or a targeted performance review, the engagement is scoped to your specific situation, not a generic template.

Book a consultation to discuss your model’s current state and what a structured improvement plan would look like. You can also review the full range of Datataal’s Fabric consulting services before getting in touch.


Sources

The sources below are the most authoritative references for the topics covered in this guide.

Microsoft Fabric blog