The core Power BI data modeling best practices are to shape the data as a star schema, relate tables one-to-many on integer keys with single-direction filters, load only the columns you use, and put calculations in measures. Power BI data model best practices are the same whatever the report: one fact table per business process, surrounded by small dimension tables. This post covers each practice, with the Power Query and model view steps to apply it.

Power BI data model best practices at a glance

1. Use a star schema: fact tables in the middle, dimension tables around them.

2. Relate tables one-to-many, from dimension to fact, with a single filter direction.

3. Join on integer ID columns, not text columns.

4. Use your own marked date table and turn off auto date/time.

5. Remove columns and tables the report does not use.

6. Put calculations in measures. Use calculated columns only for fixed row-level logic.

7. Name tables, columns, measures and Power Query steps so another person can follow them.

8. Hide keys and raw columns, and group measures into display folders.

Use a star schema

Data modeling is the work of organizing data into tables and relationships so it is easy to analyze and report on. Power BI works best with a star schema. Microsoft's own modeling guidance recommends it.

A star schema has two kinds of table:

• Fact tables store events or measurements, such as sales lines, orders or budget rows. They have many rows, numeric columns and a key for each related dimension.

• Dimension tables describe the facts, such as Product, Customer, Store and Date. Each has one row per item and a unique ID column.

Each dimension relates directly to the fact table. A filter on a dimension, such as a product category, flows along one relationship to the fact table. That makes filters predictable and DAX simpler.

Avoid chains of dimensions, where Product relates to Subcategory, which relates to Category. Merge them into one Product table instead. Our comparison of star schema vs snowflake schema explains the trade-off. Also avoid one wide flat table with everything in it. It is simple to start with, but it repeats every descriptive value on every row and makes some calculations hard to write.

How to connect tables in Power BI

You connect tables in Power BI with relationships. A relationship links a column in one table to a matching column in another, so a filter on one table also filters the other. Power BI supports three kinds of cardinality:

• One-to-many: a unique value in one table matches many rows in another. A product ID appears once in Product and many times in Sales. This is the relationship a star schema uses.

• One-to-one: a unique value matches exactly one row in the other table.

• Many-to-many: values repeat on both sides. Power BI allows this, but it needs care.

To create a relationship, open the model view and drag the key column from the dimension onto the matching column in the fact table. You can also use Manage relationships on the Modeling tab.

Power BI can also detect relationships for you. The settings are under File > Options and settings > Options, in Current File > Data Load. Autodetect is convenient for a quick look at new data. For a model that others will use, check every relationship it creates, and turn autodetect off if it keeps adding ones you do not want.

File > Options and settings > Options > Current File > Data Load controls how Power BI creates relationships.

Power BI relationships best practices

• Relate dimension to fact, one-to-many. The dimension is the "one" side. If a dimension key has duplicates, fix the dimension rather than changing the cardinality.

• Keep the filter direction single. Filters should flow from the dimension to the fact. Microsoft recommends keeping bi-directional relationships to a minimum. They can slow queries and give results that readers do not expect.

• Use bi-directional filtering only where a design needs it. The standard case is a bridge table in a many-to-many design. Our post on cross filter direction covers when "Both" is the right choice.

• Model many-to-many through a bridge table. For customers and accounts, where one customer has many accounts and one account has many customers, add a bridge table with one row per customer-account pair. Relate it one-to-many to both dimensions, and set one of those relationships to filter in both directions.

• Merge one-to-one tables. Two tables with a one-to-one relationship usually describe the same thing. Combine them into one table in Power Query.

• Use inactive relationships for a second date. Only one relationship between two tables can be active. If Sales has an order date and a ship date, make the order date relationship active and the ship date relationship inactive. Then turn it on in the measures that need it.

Sales by Ship Date =
CALCULATE (
    SUM ( Sales[Amount] ),
    USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )
)

For how cardinality affects model size and speed, see our post on Power BI relationships and cardinality.

How to connect columns from fact and dimensions

Join fact and dimension tables on ID columns. Integer keys compress well and make relationships fast. Text columns, such as product names, are larger to store and break when a name is spelled two ways.

Use a key from the source system when one exists. If a dimension has no ID column, you can create one in Power Query:

1. On the Home ribbon, select Transform data to open Power Query.

2. Select the dimension table.

3. On the Add Column ribbon, select Index Column.

4. Choose From 1 or From 0.

5. Rename the new column, for example ProductKey.

Repeat this for each dimension that needs a key. Then replace the text column in the fact table with the new ID:

1. In Power Query, select the fact table.

2. On the Home ribbon, select Merge Queries.

3. Select the dimension table and click the matching text column in both tables.

4. Keep the join kind Left Outer and select OK.

5. Expand the new column and select only the ID column.

6. Rename the ID column.

7. Remove the original text column from the fact table.

8. Select Close & Apply.

Merging the fact table with a dimension in Power Query to bring in the dimension's ID.

Now relate the fact table to the dimension on that ID column. This merge runs on every refresh. On a large fact table, it is better to add the keys upstream, in the source database or a Fabric lakehouse, and keep Power Query for light shaping.

Use a date table and turn off auto date/time

By default, Power BI creates a hidden date table for every date column in the model. This is auto date/time. With many date columns, those hidden tables add size, and they cannot be shared across fact tables.

Build one date table instead, mark it as a date table, and relate it to each fact table. Then turn off auto date/time under File > Options and settings > Options > Current File > Data Load > Time intelligence. Our guide on creating date tables in Power BI shows how to build one.

Tips and tricks for Power Query

Power Query is where you shape tables before they reach the model. Our Power Query guide covers it in depth. For data modeling, these habits matter most:

• Remove columns you do not use. Every loaded column takes memory, even if no visual uses it.

• Choose columns rather than removing them. Select the columns to keep and use Remove Other Columns on the Home ribbon. A column added to the source later then stays out of the model.

• Put key columns first. Reorder columns so IDs and foreign keys come before descriptive columns. The model does not need this, but it makes tables easier to read.

• Combine steps. Do not repeat the same transformation several times in one query.

• Rename your steps. Replace names like "Changed Type 2" with what the step does. The next person to open the query will need them.

Disable load for helper queries

Some queries exist only to feed another query, for example tables you append into one. They do not need to be in the model. To keep one out:

1. In Power Query, right-click the query in the Queries pane.

2. Select Enable load to clear the check mark.

The query name then shows in italics, which means it is not loaded into the model. Its data still flows into any query that uses it.

Clear Enable load to keep a helper query out of the model.

Use measures instead of calculated columns

Both measures and calculated columns are written in DAX, but they work differently.

• Calculated columns are computed row by row at refresh and stored in the model. They increase model size. Use them for logic that does not change with the reader's selections, such as a category flag or a key for a relationship.

• Measures are computed when a visual asks for them, in the current filter context. They are not stored, so they do not add to model size. Use them for totals, ratios, KPIs and time calculations.

When a calculation could be either, make it a measure. Our post on measures vs calculated columns goes through the choice with examples.

How to organize measures and tables

Clear names and a tidy field list make a model usable by people who did not build it.

• Rename tables to plain business names, such as Sales, Product and Customer, not source names like DIM_PRD_01.

• Hide what readers should not use. In the model view, hide key columns and the raw numeric columns that measures already summarize. Readers then pick the measure, not the column.

• Keep measures in one place. One option is to create each measure in the fact table it summarizes. Another is a dedicated measures table, which our guide to creating a measures table describes. Pick one approach and use it throughout the model.

• Name measures consistently, such as Revenue, Revenue YTD and Revenue PY.

To group measures into display folders:

1. Open the model view.

2. Select a measure.

3. In the Properties pane, type a folder name in Display folder.

4. For a subfolder, separate the names with a backslash, for example Sales\Year to date.

The Display folder property groups measures in the Data pane.

How to check the model

Review the model when the report changes or refresh gets slower. Look for unused tables and columns, relationships you no longer need, and measures that are hard to follow. Performance Analyzer, on the Optimize ribbon, shows how long each visual takes to load, which points you to the slow ones. Our Power BI performance optimization guide covers what to do next.

FAQs

When should I use calculated columns instead of measures in Power BI?

Use a calculated column when you need a value on every row that does not change with the reader's filters. Examples are a classification, a flag, or a key used in a relationship. Use a measure for anything that should respond to filters, such as totals, ratios and KPIs. If the logic can be done in Power Query or the source, that is usually better still, because the result is loaded like any other column.

How do calculated columns and measures affect Power BI performance?

Calculated columns are stored in the model, so each one adds to its size and to refresh time. Too many make the model larger and slower. Measures are not stored. They are computed when a visual needs them, so they add no size, but a complex measure can make a visual slow to load. Keep columns few, and keep measure logic simple enough that Performance Analyzer shows reasonable times.

Sources

• Understand star schema and the importance for Power BI - Microsoft Learn

• Many-to-many relationship guidance - Microsoft Learn

• Bi-directional relationship guidance - Microsoft Learn

• Active vs inactive relationship guidance - Microsoft Learn

• Auto date/time guidance in Power BI Desktop - Microsoft Learn