Power BI Data Model: Design, Relationships, and Analysis

Are your reports feeling sluggish?

Are your visuals taking forever to load?

Brace yourself – it’s time to optimize and inject some flavor with Power BI data models.

Now, I know what you’re thinking. Data models? Optimization? Isn’t that stuff for the nerds in the IT department?

Well, my friend, you couldn’t be more wrong. Common data model Power BI is the secret sauce that can turn your run-of-the-mill reports into powerhouses of information. They allow you to efficiently organize and structure your data, making it easier to analyze and visualize.

Forget about spending hours waiting for your data to load. With a suitable data model, your insights will be delivered in the blink of an eye. It’s like having a Ferrari for your reports while others are stuck with a rusty old bicycle.

And here’s the best part: you don’t have to be a tech genius to make it happen. We’ll walk you through the steps so you can become a Power BI desktop data modeling maestro in no time.

Table of Content:

1. What is Power BI Data Modeling?
2. Video Tutorial: How to Show a Data Model in Power BI
3. Why are Power BI Data Models Important?
4. What is the Purpose of Data Modeling in Power BI?
5. Types of Data Modeling in Power BI
6. How to Choose a Data Modeling Tool?
7. How to Create a Data Model in Power BI?
   • Step 1: Import Data
   • Step 2: Transform Data
   • Step 3: Define Relationships
   • Step 4: Organize with Fact and Dimension Tables
   • Step 5: Create Calculated Columns & Measures
   • Step 6: Add Hierarchies
   • Step 7: Validate the Model
   • Step 8: Build Reports
8. Power BI Data Model Examples
9. How to Visualize Data Modeling in Power BI?
10. Top 5 Benefits of BI Data Modeling
11. Power BI Data Modeling Best Practices
12. Common Challenges in Data Modelling in Power BI
13. Power BI Data Models – FAQs
14. Wrap Up

First!

What is Power BI Data Modeling?

Definition: Power BI data modeling is the process of structuring and connecting data from multiple sources to create a logical foundation for reporting and analysis. It involves organizing data into tables, defining relationships, and using calculations (like DAX) to uncover deeper insights.

A well-designed model, often based on a star schema, ensures accuracy, improves performance, and makes it easier to build interactive dashboards and reports that support better business decisions.

Key Components of a Data Modeling for Power BI

• Tables
  • Fact Tables
    Store measurable, numeric data such as sales, revenue, or transactions. They represent the core business activities.
  • Dimension Tables
    Provide descriptive context to fact tables, like product details, customer info, or time periods, helping with meaningful analysis.
• Relationships
  Define how fact and dimension tables connect, enabling accurate filtering, aggregation, and insights across multiple data sources.
• Calculated Columns & Measures
  Created using DAX (Data Analysis Expressions) to add custom logic, perform calculations, or create KPIs directly in the model.
• Hierarchies
  Allow drill-down into different levels of data (e.g., year → quarter → month), making reports more interactive and insightful.

Video Tutorial: How to Show a Data Model in Power BI

Why are Power BI Data Models Important?

Power BI data models are important because they act as the backbone of reporting and analysis. A strong data model:

1. Improves Accuracy: Ensures consistent calculations and reliable insights across reports.
2. Boosts Performance: Optimized models handle large datasets quickly and efficiently.
3. Simplifies Analysis: By organizing data into logical tables and relationships, users can explore information without confusion.
4. Enables Advanced Insights: DAX formulas and measures in the model unlock deeper, customized analysis.
5. Supports Better Decisions: With clear and trustworthy data, businesses can make informed, data-driven choices.

What is the Purpose of Data Modeling in Power BI?

1. Organize Data: Converts raw data into a structured, easy-to-use format.
2. Enable Insights: Supports meaningful visualizations and analysis for better decision-making.
3. Improve Efficiency: Optimizes queries and speeds up report performance.
4. Maintain Consistency: Applies uniform business rules and calculations across reports.
5. Ensure Governance: Documents data usage and supports compliance within the organization.

Types of Data Modeling in Power BI

• Star Schema

This is the most common and recommended model. It has a central fact table (like sales or revenue) connected to multiple dimension tables (such as products, customers, or dates). It’s simple, efficient, and easy to understand.

• Snowflake Schema

Similar to a star schema, but dimension tables are further broken down into sub-dimensions. This creates a more normalized structure, which can reduce redundancy but may be harder to manage.

• Flat Table (Denormalized Model)

All data is kept in one large table without splitting into facts and dimensions. While it’s easy for very small datasets, it’s not ideal for performance and flexibility when working with large or complex data.

• Galaxy Schema (Fact Constellation)

In more complex scenarios, multiple fact tables share dimension tables. This model is useful when you’re analyzing different processes (like sales and inventory) that rely on shared dimensions.

How to Create a Data Model in Power BI?

• Step 1: Import Data

Open Power BI Desktop → click Get Data → load tables from Excel, SQL Server, or other sources.

• Step 2: Transform Data

Use Power Query to clean, format, and prepare your data (rename columns, change data types, remove duplicates).

• Step 3: Define Relationships

In Model View, connect tables with relationships using primary and foreign keys. Set the correct relationship type (one-to-many, many-to-one).

• Step 4: Organize with Fact and Dimension Tables

Separate data into fact tables (transactions) and dimension tables (descriptions) for a clear star schema.

• Step 5: Create Calculated Columns & Measures

Use DAX to build custom calculations (e.g., Total Sales = SUM(Sales[Amount])).

• Step 6: Add Hierarchies

Set up drill-down paths such as Year → Quarter → Month to make analysis easier.

• Step 7: Validate the Model

Check relationships and measures for accuracy. Confirm results by testing with sample visuals.

• Step 8: Build Reports

Use your completed model to create interactive dashboards and share insights in Power BI.

Power BI Data Model Examples

• Sales Data Set

A sales data set in Power BI collects data related to sales activities. It includes information such as sales revenue, quantity sold, customer details, product details, and other relevant metrics. This data set helps to analyze and visualize sales performance, identify trends, and track key performance indicators (KPIs). Consequently, helps to make data-driven decisions to improve sales strategies and outcomes.

Example

Let’s say you have the company sales data table below.

Store	Category	Items	Brand	Unit Sold
Online Store	Electronics	Mobile	Samsung	39
Online Store	Electronics	Tablet	Samsung	73
Online Store	Electronics	Laptop	Dell	156
Online Store	Garments	Jeans	Levi’s	46
Online Store	Garments	T-Shirt	H&M	104
Online Store	Garments	Jackets	Puma	41
Online Store	Furniture	Sofa	IKEA	73
Online Store	furniture	Chair	Kartell	46
Online Store	furniture	Desk	Stickley	43

You can appreciate how the report has presented this information, making the gleaning of insights effortless.

• Spending Data Set

A spending dataset consists of information about an organization’s planned and actual costs.

It helps companies analyze and compare their budgeted and actual expenses. The data set includes details such as cost categories, budget amounts, actual expenditures, and variances. It helps to identify budget deviations, track spending trends, and make informed financial decisions.

Additionally, it can support cost-of-living comparison by city, allowing organizations to evaluate expenses across different locations and plan budgets accordingly.

Example

Suppose you have a company spending data set below.

Total Spend	Department	Category	Spend Amount ($)
Total Spend	Marketing	Advertising	20,000
Total Spend	Marketing	Events	15,000
Total Spend	Marketing	Collateral	30,000
Total Spend	Marketing	Salaries	50,000
Total Spend	Operations	Rent	10,000
Total Spend	Operations	Utilities	8,000
Total Spend	Operations	Supplies	15,000
Total Spend	Operations	Salaries	40,000
Total Spend	Sales	Salaries	30,000
Total Spend	Sales	Commissions	6,000
Total Spend	R&D	Salaries	40,000
Total Spend	R&D	Contractors	20,000
Total Spend	Admin	Salaries	30,000
Total Spend	Admin	Legal	15,000
Total Spend	Admin	IT	10,000

You can present it in a Power BI report, as shown below, to make the analysis easy.

How to Visualize Data Modeling in Power BI?

We’ll break down the process into five distinct stages.

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email. Click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in”.

• Choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Sankey Chart

• Click on the “Create” option on the left-side menu.
• Select ”Paste or manually enter data“.

• We’ll use the following cash flow data for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the above data table into the “Power Query” window.
• Select the “Create a dataset only” option.

• Click on the “Data Hub” option on the left-side menu.
• Power BI populates the data set list. (If you have not created a data set, refer to the Error! Reference source not found section.)

• Choose a data set for the Sankey chart.
• PBI populates the screen as shown below:

• Click on the “Create a report” dropdown.
• Select “Start from scratch“.

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

• Creating the Sankey Diagram requires us to use an add-in or Power BI visual from AppSource.
• Navigate to the right side of the Power BI dashboard.
• Open the Power BI Visualizations panel.
• Click the ellipsis symbol (…) as highlighted in the diagram below. This will import the Power BI Sankey Diagram extension by ChartExpo.

• The following menu opens:
• Select the “Get more visuals” option.

• The following window opens.
• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box.
• You should see the “Sankey Diagram for Power BI by ChartExpo”, as shown in the image below.

• Click the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.
• The following window opens in the report section of your dashboard:

• You can resize the visual as needed.
• Navigate to the right side of your Power BI dashboard.
• You should see “Fields” next to “Visualizations.”

• You’ll select the fields to use in your Sankey chart here.
• The ChartExpo visual needs to be selected, though. Select the fields in the following sequence:
  • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll be asked for a ChartExpo license key or email address.

Stage 5: Activate your ChartExpo Trial or Apply a Subscription Key

• Select the ChartExpo visual.
• You should see three icons below “Build Visual” in the Visualizations panel.

• Select the middle icon, “Format visual.”
• The visual properties will be populated as shown below.

• To begin using ChartExpo as a new user;
  • Enter your email address in the textbox under the “Trial Mode” section. ChartExpo will send the License key to this email upon subscribing to the add-in.
  • Ensure you provide an accurate and up-to-date email address.
  • Toggle “Enable Trial” to activate your 7-day trial.

• You should receive a welcome email from ChartExpo.
• If you do not find the email in your inbox, kindly check your spam folder.
• The Sankey Diagram you create under the 7-day trial contains the ChartExpo watermark.

• If you have obtained a license key:
  • Enter your license key in the “ChartExpo License Key” textbox in the “License Settings” section (see below).
  • Slide the toggle switch next to “Enable License” to “On“.

• Your Sankey Diagram will then appear without a watermark.

• Let’s add a Prefix (such as a $ sign) with the numeric values in the chart.
• Expand the “Stats” properties and include the Prefix value.

• Let’s add colors to each node. Expand the “Level Colors” properties and select the colors.

• Automatically all changes will be saved.

Insights

• The procurement cost at Level 1 amounts to $155K.
• At Level 2, $72.3K (46.7%) of the total cost was allocated to the supplier, while $82.4K (53.3%) was spent on subcontractors.
• At Level 3, the supplier cost of $72.3K was divided between Power-up Builder ($53.0K) and Five-star Construction ($19.3K).
• The subcontractor cost of $82.4K was distributed among Skyline Contractors ($25.6K), Onyx General Contractors ($30.7K), and Living Well Remodeling ($26.0K).
• At Level 4, the supplier companies supplied raw materials worth $72.3K, while Mechanical Installations accounted for approximately $82.4K.
• Within the raw material cost, cement, steel, bricks, and timber accounted for $20.6K, $32.5K, $10.3K, and $9K, respectively.
• From the mechanical installation cost, Plumbing, Heating, Mechanical Work, and Welding accounted for $48.1K, $22.9K, and $11.5K, respectively.

Top 5 Benefits of BI Data Modeling

• Improved Data Organization

BI data modeling structures raw data into fact and dimension tables, making it easier to navigate, analyze, and understand.

• Faster and More Accurate Reporting

With a well-designed model, reports load faster, calculations run efficiently, and insights are more reliable.

• Consistent Business Logic

Power BI desktop data modeling ensures uniform definitions, calculations, and KPIs across reports, reducing errors and misinterpretation.

• Better Decision-Making

By transforming data into meaningful insights, BI data modeling empowers leaders to make informed and strategic decisions.

• Scalability and Flexibility

A strong model adapts as data grows or business needs change, ensuring long-term usability without slowing performance.

Power BI Data Modeling Best Practices

• Use a Star Schema Design

Organize your model into fact and dimension tables. Fact tables store transactions or numeric values, while dimension tables hold descriptive details. This structure improves performance and makes analysis more intuitive.

• Keep Relationships Simple

Limit relationships to one-to-many whenever possible. Avoid unnecessary bi-directional filters, as they can create ambiguity and slow performance.

• Optimize Column Data Types

Remove unused columns, choose the most efficient data types (e.g., Whole Number instead of Decimal), and avoid storing unnecessary text fields. This keeps your model lighter and faster.

• Leverage DAX Measures Instead of Calculated Columns

Where possible, create measures rather than calculated columns. Measures are more efficient, reduce memory usage, and provide flexibility in analysis.

• Create Hierarchies for Better Navigation

Set up hierarchies (e.g., Year → Quarter → Month or Category → Subcategory → Product) to help users drill down easily and make reports more user-friendly.

Common Challenges in Data Modelling in Power BI

• Handling Large and Complex Data

Working with massive datasets can slow performance and make models harder to manage if not optimized properly.

• Defining the Right Relationships

Incorrect or missing relationships between tables often lead to inaccurate results and misleading reports.

• Managing DAX Complexity

Writing efficient DAX formulas can be challenging, especially when building advanced calculations or optimizing performance.

• Ensuring Data Accuracy and Consistency

Inconsistent data sources or poorly cleaned data can cause discrepancies, making insights less reliable.

• Balancing Flexibility and Performance

A highly flexible model may become too complex, while an overly simplified model might not meet all reporting needs.

Power BI Data Models – FAQs

What is the best data model for Power BI?

The Star Schema is considered the best data model for Power BI. It organizes data into fact tables (numeric values like sales or revenue) and dimension tables (descriptive details like customer, product, or date). This structure improves performance, simplifies relationships, and makes reporting more intuitive.

What is the difference between dataset and data model in Power BI?

• Dataset: A collection of data loaded into Power BI from various sources (Excel, SQL, APIs, etc.). It can include raw or transformed data.
• Data Model: The structured version of a dataset where relationships, measures, hierarchies, and calculations are defined to support analysis and reporting.

What are the four types of data models?

1. Conceptual Data Model: High-level overview showing entities and relationships, used for planning.
2. Logical Data Model: More detailed, showing attributes, primary keys, and relationships without focusing on implementation.
3. Physical Data Model: Implementation-specific, defining how data is stored in databases with tables, columns, and constraints.
4. Dimensional Data Model: Used in BI tools like Power BI, often in a Star Schema or Snowflake Schema, for reporting and analytics.

How do I open the Power BI data model file?

Launch Power BI, navigate to “File,” and select “Open” to locate and load your .pbix file. The data model, visuals, and settings will be accessible for further editing and analysis.

Wrap Up

The Power BI data model is the cornerstone of building insightful and high-performing reports and dashboards. Designing tables, establishing meaningful relationships, and employing calculated measures lay the foundation for extracting profound insights from data. This optimization ensures your reports deliver accurate and relevant information, empowering you to make informed decisions.

A robust data model not only enhances data integrity but also facilitates smoother data transformation processes. Through thoughtful planning, you align your model with business requirements, unraveling intricate patterns and correlations within your data. The result? Clearer visualizations that succinctly convey the story hidden in the numbers.

ChartExpo’s prowess helps you create appealing, interactive visualizations that effectively communicate data patterns and variances. This makes it easier to understand the data and enables you to present your findings clearly and impactfully.

Therefore, leveraging the power of Power BI data models and ChartExpo facilitates data-driven decision-making. Consequently, helps you achieve financial success and gain a competitive edge in today’s fast-paced business landscape.

Start optimizing your reports today and unlock the full potential of your spending data set with Power BI and ChartExpo.