Understanding What Data Modeling Is And Creating The Right Relationships

After having an extensive discussion about the query editor, it’s time to move all that data into a Power BI report through data modeling. You may watch the full video of this tutorial at the bottom of this blog.

Data modeling allows you to connect different data tables in your Power BI report by creating relationships between them.

Fixing Relationships In The Core Data Model

My core data model consists of all the tables I fixed in the query editor. This includes my Customers table, Locations table, Products table, etc.

data modeling

Since I’ve cleaned up those tables and I’m sure that I have all the data I need for now, I’m going to click on Close & Apply to close the query editor and apply any pending changes.

Of course, this doesn’t mean that I won’t be going back into the query editor once I start working on data modeling. I would definitely be coming back into it a lot, especially if I need to make additional transformations or if I need to bring in new data depending on what my reports require.

The process of applying the queries into my Power BI model might take some time, depending on the amount of data I have.

data modeling

But once the application is done, I’m going to end up on a fresh canvas where I can start creating my analysis.

Some people may jump straight into this part. But what I usually do is check the relationships built around the data I worked on when I was in the query editor.

data modeling

This is the modeling area, which I can access through this particular icon here.

data modeling

If you have no Power BI experience and were previously working on Excel, this will be completely unfamiliar to you. It’s important that you understand this part because it is crucial in developing anything within Power BI.

This is where I can make sure that the relationships across my data are set up in an optimal manner. Otherwise, it will be harder to get any calculation or analysis done correctly.

The relationships are represented by these lines and arrows running across the data tables.

data modeling

Power BI normally guesses the relationships among the different tables automatically. Unfortunately, Power BI gets this part wrong most of the time.

So I usually start off by deleting these existing relationships, especially when I’m 100% unsure if the default relationships Power BI has placed make sense.

data modeling
data modeling

In Excel, you would normally have one huge file with hundreds of columns. But data modeling within Power BI is different.

Here, for example, I have a core set of information, and then I have separate supporting tables that have filtering information within them. So I end up with different tables with different data groups in each one.

data modeling

This is the critical part in data modeling – identifying whether each table is a lookup table or a fact table. It’s important to understand what these two types of tables do so that you can understand what kind of relationships you can draw through them.

A fact table carries all the transactions, like in this sales table.

On the other hand, a lookup table carries filtering information.

Once I’ve identified where my tables belong, I organize them. I like lining up my lookup tables here on top in a single row.

As for my fact table, I like putting it below.

Of course, some people might do it differently. But this is the best practice I want to share because I have a philosophy of keeping things as simple as possible.

I layer these tables like this because I want to visualize how the relationships go. I call this a waterfall of filters. This way, I have my lookup tables on top sending filtering information down into the raw data that I’m working with.

***** Related Links *****
Top 3 Best Practices To Organize Your Power BI Models
How To Organize Your Power BI Data Model – Best Practice Tips
Customer Segmentation Techniques Using The Data Model – Power BI & DAX


This is very basic stuff, but it’s crucial that you master this because it can make or break your calculations later on.

Understanding the basics of data modeling helps you make sure you’re getting the right results in your reports. It will save you the frustration of trying to find the root cause of errors that might result from the wrong relationships.

All the best,


Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More