Data model best practices in Power BI - Step-by-Step Guide

Introduction

A good data model helps your reports run faster and be easier to understand. It organizes your data so you can find answers quickly without confusion or errors.

When you want your sales report to update quickly even with lots of data

When you need to combine data from different sources like Excel and databases

When you want to avoid mistakes caused by duplicate or missing data

When you want to create clear relationships between tables for easy analysis

When you want to make your report easy for others to use and maintain

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

You see your current data model and tables

💡 Start with a clean model before adding new data

Step 2: Click on the Model view icon

- Left vertical pane

You see all tables and their relationships visually

💡 Use this view to check how tables connect

Step 3: Select a table and review its columns

- Model view canvas

You see all columns and their data types

💡 Remove any columns you don’t need to keep the model simple

Step 4: Create relationships by dragging a column from one table to another

- Model view canvas

A line appears showing the relationship between tables

💡 Make sure relationships are one-to-many and use unique keys

Step 5: Set the correct data types for each column

- Model view > Column properties pane

Columns have proper types like text, number, or date

💡 Correct data types improve performance and accuracy

Step 6: Hide unnecessary columns from report view

- Model view > Right-click column > Hide in report view

These columns won’t appear in the fields list for report building

💡 Hiding columns reduces clutter for report creators

Step 7: Use star schema design by having fact tables connected to dimension tables

- Model view

Your model is easier to understand and faster to query

💡 Avoid circular relationships and many-to-many where possible

Before vs After

Before

Model has many columns including unused ones, unclear relationships, and mixed data types causing slow report refresh and confusing fields list

After

Model has only needed columns, clear one-to-many relationships, correct data types, and hidden technical columns resulting in faster reports and easier report building

Settings Reference

Data type

📍 Model view > Column properties pane

Defines how Power BI treats the data for calculations and filtering

Default: Text

Relationship cardinality

📍 Model view > Relationship line > Properties pane

Defines how tables relate to each other for accurate data filtering

Default: One to many

Cross filter direction

📍 Model view > Relationship line > Properties pane

Controls how filters flow between related tables

Default: Single

Hide in report view

📍 Model view > Right-click column or table

Controls whether columns or tables appear in the report fields list

Default: Visible

Common Mistakes

Keeping all columns from source tables even if not used

Extra columns slow down the model and clutter the fields list

Remove or hide columns that are not needed for analysis

Creating many-to-many relationships without a bridge table

This can cause incorrect results and slow performance

Use a bridge or dimension table to create one-to-many relationships

Using text data type for numeric or date columns

Power BI cannot perform calculations or time intelligence correctly

Set correct data types like number or date for those columns

Summary

A clean data model improves report speed and clarity

Use one-to-many relationships and star schema design

Remove or hide unused columns and set correct data types