Mastering Data Modelling in Power BI: A Beginner’s Guide

What is Data Modelling?

Data modelling in Power BI is like organising a messy closet—you’re taking all the scattered pieces (your data) and arranging them in a way that makes sense and is easy to use. Think of it as creating a custom blueprint that tells Power BI how your data connects and works together. You’re setting the rules for how different pieces of information (tables) link up!

Without a good data model, Power BI can’t create accurate calculations or visuals. A clear model makes everything work smoothly and ensures your reports are reliable.

Here’s what we’ll get into:

Data Connections

Model View

Relationship Cardinality

Cross Filter Directions

Working with Keys

Data Connections

The first thing you’ll do in PowerBI is connect to a data source. There’s a myriad of ways you can get data into PowerBI, but they’re classified under three distinct types:

‍

• Live Connection
  Think of this as a live stream of your data. Power BI stays connected directly to your data source (like a database) and shows updates in real time. It’s perfect when you need the freshest numbers possible, without delays. But here's the catch—you can’t tweak the data much in Power BI since it’s being read straight from the source.
• DirectQuery
  Similar to Live Connection, but with more flexibility! Power BI doesn’t store the data—it queries (or asks) the source every time you interact with a report. This mode is handy for big datasets that are too large to import. You can refresh or explore your data without putting a strain on your computer, though some features may be slower compared to imported data.
• Import Data
  Here, Power BI actually brings your data into its system. It’s like downloading it to your device, so it works offline and loads super fast. You get full control to shape, edit, and play around with your data. It’s best for smaller datasets or when you don’t need real-time updates.

For a comprehensive list of possible connections, click here.

Relationships

Relationships in data analysis are like the connections between people in a social network—they show how things are related to each other in your data. These relationships help us understand how different pieces of information interact and work together, much like knowing whose friends with whom can give you insights into a group of people.

What Are Relationships in Data?

Think of relationships in data as the links between tables or datasets that share something in common. For example, imagine you have two lists:

1. One list has student names and IDs.
2. Another list shows grades, but it only uses student IDs instead of names.

The relationship here is based on the Student ID because it appears in both lists. Connecting these two lists allows you to see both a student's name and their grades in one place.

Why Do Relationships Matter?

Relationships matter because they help you analyse data more effectively. They:

• Combine Information: Like joining puzzle pieces, relationships pull data together to create a full picture.
• Save Time: Instead of manually hunting for links, building clear relationships makes data analysis faster.
• Give Meaning: Relationships allow you to ask more meaningful questions like, “What’s the average grade by class?” or “How much did customers spend based on region?”

Without relationships, you’d just have a bunch of disconnected lists of data that don’t tell the full story.

Schemas

Think of a schema in Power BI like the blueprint for a building—it’s the plan that shows how all the data is organized and connected. Just like an architect needs a layout to build a sturdy structure, Power BI needs a schema to make sense of your data and turn it into meaningful reports.

A schema is the structure of your data model. It shows:

1. What tables you have (e.g., Customers, Sales, Products).
2. What’s inside those tables (columns, like “Customer Name” or “Order Amount”).
3. How the tables are linked (relationships between them).

Why Should You Care About Schemas?

Schemas help Power BI users (a.k.a., you) by:

• Organizing Your Data: Instead of a big messy pile, your data gets sorted into clear buckets (tables) with logical connections.
• Making Analysis Simple: You’ll understand how different parts of your data (like customers and sales) interact, so you can ask better questions and get clearer answers.
• Preventing Chaos: Without a good schema, things can get complicated fast, leading to confusing reports and incorrect insights. A clear schema keeps everything under control.

Breaking It Down with an Example

Imagine you run a coffee shop. Your schema might include:

1. A “Products” table for all the drinks and pastries you sell.
2. A “Sales” table for tracking what customers buy.
3. A “Customers” table for—well, your customers!

How do they connect? Your schema ties them together:

• "Sales" links to "Products" via a Product ID (to figure out what was bought).
• "Sales" links to "Customers" via a Customer ID (to know who bought it).

These connections (called relationships) are the backbone of your schema. They allow you to answer questions like, “Which product is most popular among VIP customers?”

Star and Snowflake Schemas—What’s That About?

Schemas come in styles too, depending on how your data is laid out:

• Star Schema (Simple and Awesome):
  Your main table (e.g., Sales) sits in the middle, with smaller, related tables (like Customers or Products) branching out around it, kinda like a star. It's fast and easy to use!
• Snowflake Schema (More Detailed):
  This takes things further by splitting tables into even smaller parts. For example, instead of a single “Products” table, you might have separate tables for “Product Categories” and “Product Details.” It’s useful, but a bit more complex.

Pro Tips for Power BI Schemas

1. Stick With Star If You’re New
   Star schemas are easier to build and work well in most cases. They’re perfect for beginners and speed up your reports.
2. Use Clear Names
   Label tables and columns clearly (like “Customer Email” instead of just “Email”) to save yourself—and others—headaches later.
3. Clean Your Data First
   Schemas only work well if your data doesn’t have junk. Make sure there aren’t duplicates or missing values before you start.

‍

Cardinality

There are three main types of relationships you’ll run into:

1. One-to-One (1:1)
   One item in a table is connected to exactly one item in another table.
   ☕ Example: Imagine each employee has a unique ID, and another table stores their work email. There’s only one email per employee, so it’s a one-to-one relationship. 

In a schema, these aren’t common and is a sign that your schema could be better optimised because of the storage of redundant data.

2. One-to-Many (and Many-to-One) (1:N)
   One item in a table is connected to MANY items in another table.
   🍎 Example: A school has one teacher who teaches multiple students. The teacher ID in the first table connects to many student IDs in the second.
3. Many-to-Many (M:N)
   Many items in one table are connected to many items in another table.
   🏀 Example: A basketball player can belong to multiple teams over their career, and a team has multiple players. The relationship works both ways!

Cross filter direction

Cross filter direction controls how filters flow between connected tables in your data model. It decides whether a filter applied on one table affects just the other table, or whether both tables can filter each other.

Power BI offers two options:

1. Single Direction
2. Both Directions

This is the default setting and works like your typical one-way street. Filters travel from one table to another, but not the other way around.

Example:
You have two tables:

• A "Products" table (listing all the items for sale).
• A "Sales" table (tracking which products were sold).

With single direction filtering, if you apply a filter to "Products" (e.g., only show "Books"), it flows to the "Sales" table (so now you only see sales of books). But if you filter "Sales" (e.g., only show orders from January), it won’t send that filter back to the "Products" table.

Use Single Direction when:

• One table provides the “master list” (like Products or Customers) and you don’t need filters traveling both ways.
• You want better performance. Single direction is faster for Power BI to process.

Both Directions Cross Filtering

Now, imagine it’s a two-way street or a walkie-talkie conversation. Filters move in BOTH directions between the tables. If you filter the first table, it affects the second one. And if you filter the second table, it affects the first one.

Example:
Take the same "Products" and "Sales" tables. With both directions enabled:

• If you filter "Products" to only show "Books," it still flows to "Sales."
• BUT, if you filter "Sales" (e.g., orders from January), it also flows back to "Products," showing you only the products sold in January.

Use Both Directions when:

• You have connected tables that need to interact in both ways, like in complex models.
• You’re working with scenarios like a snowflake schema or an uncertain hierarchy where filters between tables should flow both ways.

Why Choose One Over the Other?

Here’s the deal—it’s all about efficiency and clarity.

• Stick to single direction as much as possible. It’s simpler, runs faster, and reduces the chance of creating data confusion (like unexpected filter overlaps).
• Use both directions carefully. It’s powerful but can slow things down or create ambiguous relationships if not handled correctly.

Relatable Example to Tie It Together

Pretend you own a bookstore. Here’s your setup:

1. A "Books" table (listing all the books in the store).
2. A "Sales" table (tracking transactions).

You filter "Books" to show only "fiction titles." That filter flows to your "Sales" table, showing only sales of fiction books. This is typical single direction filtering.

But imagine you want to check which fiction books were sold in January. With both directions, you filter "Sales" for January. The filter flows back to the “Books” table, narrowing it down to only the fiction books sold that month.

Pro Tips for Cross Filter Direction in Power BI

1. Start with Single Direction to keep it simple.
2. Use Both Directions Strategically, like for scenarios with complex interconnections (e.g., when working with many-to-many relationships).
3. Double-Check Ambiguous Paths. If relationships can flow in more than one way, Power BI might throw a warning, or things can get messy!

‍

The Model View

PowerBI’s model view is a visual representation of the schema.

Let’s look at some of the important aspects of the model view:

Creating Separate Diagrams

When you have a lot of tables with many relationships, it can be confusing, right? Thanks to a recent update (November 2024), you can now create different views that contain subsets of the tables in your model. 

Clicking the + button creates a new canvas for you, where you can add the tables you want to view. 

Add related tables

If you want to show all tables that have a relationship with a certain table, you can right click the table in the diagram or click on the 3 dots, then select Add related tables.

‍

Relationships

There are multiple ways to create or modify relationships in PowerBI.

One method is by selecting Manage relationships in the ribbon as shown in the following image. You can also access this interface by right clicking any table, or clicking the three dots, and selecting manage relationships.

‍

Creating New Relationships

You can create a new relationship by clicking on the new relationship button.

You can also create a new relationship by dragging a column from one table to another.

PowerBI automatically suggests columns to be linked if the column names are the same. Note that both columns should be of the same data type.

Either way, you will now see this interface: 

‍

We have covered cardinality and cross filter direction earlier. 

Security filters are related to Row Level Security (more on that later). 

Assume Referential Integrity  allows Power BI to optimize queries (how it fetches and combines data) by assuming the relationships between your tables are rock solid—meaning all data lines up perfectly with no missing or mismatched pieces.

When Power BI assumes referential integrity, it can create inner joins between your tables instead of outer joins (fancy database speak for saying, "Fetch only data that matches, instead of padding out the blanks for mismatched rows"). Inner joins are faster and more efficient for processing your data.

Basically, by assuming your tables line up, Power BI skips unnecessary checks and runs your reports a whole lot faster.

Model Explorer

The Model Explorer is a new feature in PowerBI that shows a tree view of the semantic model. 

Roles and Security

Security in PowerBI is all about keeping your data safe while giving the right people the information they need.

Think of roles the same way you would think of them in the real world. When it comes to security, you want to ensure that each role has access to only the information they need. When it comes to PowerBI, roles are essentially presets of different security policies you create.

Row-Level Security (RLS)

Row-Level Security makes sure that people only see the specific “rows” of data they are allowed to access. With RLS, you’re putting data under lock and key—except you’re the one setting the rules for who gets a key and what doors it opens.

Here’s how RLS functions in a nutshell:

1. Define Rules: You create filters, such as “This person can only see sales data for the West region.”
2. Assign Roles: Add users or groups to these rules (e.g., assigning West Region Managers to the “West Only” role).
3. Filter Data Automatically: Every time someone opens your report, Power BI checks those rules and only serves up the data they’re allowed to see.

‍

Creating a role

1. To create a role, select Manage Roles from the modelling tab.

2. Next, select New to create a new role in the Manage roles window.

3. Give the role an appropriate name.
4. Now select the table where you want to apply Row Level Security.
5. Go to Filter data and use the default editor to set up your roles. The rules you create here will decide if something is "true" (included) or "false" (left out).

You can also use the DAX editor to define your role:

6. Once you’ve defined the rule, click Save. Congratulations! Your role is now ready.
7. Publishing: After creating your roles, save and publish your report to the Power BI Service (that cloud-based platform where others view reports).Go to the Home tab and click Publish.

Why?
Roles only really come to life when the report is in the service. This step ensures everyone gets access to the report based on your rules.

8. Assign Users to the Role

Now, head over to the Power BI Service (access it online through your browser):

1. Find your published dataset.
2. Click on the three dots (…), then choose Manage Permissions.
3. Under Row-Level Security, assign users or security groups to your role.

Example:
Add all West Region Managers to the “West Region Manager” role, and they’ll only see sales for the West.

9. Before you finish, double-check your work.

1. Go back to Power BI Desktop.
2. Click Modeling > View as Roles.
3. Select the role you created, and Power BI will show you the report as if you were part of that role.

This ensures your filters are working exactly how you intended.

And that’s it—you’ve just created a role in Power BI! Roles are like gatekeepers that make sure everyone sees just the data they need and nothing more.

Conclusion

Data modelling in Power BI isn't just a technical step—it’s the foundation of powerful, accurate, and efficient reporting. By organizing your data with thoughtful relationships, calculated columns, and clear hierarchies, you lay the groundwork for insights that truly matter. A well-structured model transforms messy datasets into clear stories, empowering better decision-making for you and your team.

Remember, every great dashboard starts with a solid model. The time you invest in creating a clean and optimized model will pay off with smoother workflows and smarter reports. Whether you're just starting out or looking to refine your approach, don’t shy away from experimenting and learning as you go.

‍