Creating a Semantic Model in Power BI

Make sure to subscribe our BIPro Newsletter so you never miss a key update in the data world. Join over 35K+ BI lovers and get tips from those who’ve cracked tough data challenges!

Introduction

A well-designed semantic model is the foundation of every effective Power BI report. It defines how data tables connect, how calculations behave, and how insights are ultimately presented to users. Without a solid semantic model, even the most visually appealing reports can produce misleading or inconsistent results. In this article, you’ll learn how to create and explore a semantic model in Power BI, understand the Model view, and build relationships that allow data to aggregate correctly across tables, setting the stage for accurate analysis and meaningful business insights. 

Technical requirements

The following are needed in order to successfully complete the instructions provided in this chapter:

• An internet connection
• Microsoft Power BI Desktop
• Chapter 5 Start.pbix downloaded from GitHub at https://github.com/PacktPublishing/Learn-Microsoft-Power-BI_3E

Creating a semantic model

The concept of a semantic model, data model, or dataset is fundamental to Power BI. In short, a semantic model is defined by the tables that are created from Power Query queries, the metadata (data about data) regarding the columns within the tables, and finally, the relationships that are defined between tables. Relationships are needed to connect individual tables to one another. In Power BI, the semantic model is stored within an Analysis Services tabular cube. It is the creation of this semantic model that enables self-service analytics and reporting.

In Chapter 4, Connecting to and Transforming Data, we connected to various sources of data (three different Excel files) that in turn created seven different queries, which ultimately resulted in four queries that loaded data tables into our semantic model. We can now stitch those individual tables, along with our previously created data table, into a cohesive semantic model that is used for further analysis.

If you are continuing from Chapter 4, continue to use the same Power BI Desktop file after completing that chapter. Otherwise, download and load Chapter 5 Start.pbix as specified in the Technical requirements section of this chapter.

Touring the Model view

So far, we have explored the overall architecture of Power BI Desktop and Power Query Editor. We will now explore the Model view within Power BI Desktop. To switch to the Model view, click the third icon from the top in the Views bar of Power BI Desktop.

The Model view is shown in the following screenshot:

Figure 5.1 – Model view

The Model view provides an interface for building our semantic model. It does this by creating relationships between tables, as well as defining metadata for tables and columns. We can even create multiple layouts for our semantic model. The Model view interface is similar to other Desktop views, as shown in Figure 5.1:

• Header: As shown in Figure 5.1, the Header area is identical to what was described in the Touring the Desktop section of Chapter 3, Up and Running with Power BI Desktop.
• Ribbon: The Ribbon is nearly identical to what we described in the Touring the Desktop section of Chapter 3, Up and Running with Power BI Desktop, with the notable exception that only three tabs are available—that is, File, Home, and Help. If third-party extensions are installed, the External tools tab is also present.
• Views: The Views area is identical to what was described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop.
• Canvas: As mentioned in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, when in the Model view, this area displays layouts of tables within the semantic model, as well as their relationships to one another. A default All tables layout is created automatically.
• Panes: As described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, only two panes are present—the omnipresent Data pane and the Properties pane. The Properties pane is exclusive to the Model view and allows us to associate metadata with various fields or columns within the tables of the semantic model. This includes the ability to specify synonyms and descriptions, as well as data types, data categories, and default aggregations or summarizations.
• Layouts: The Pages area from the Report view is replaced by Layouts. The Layouts area allows us to create multiple layouts or views of the data tables within the model, as well as to rename and reorder the layouts.
• Footer: As described in the Touring the desktop section of Chapter 3, Up and Running with Power BI Desktop, in the Model view, the Footer area provides various viewing controls, such as the ability to zoom in and out, reset the layout, and fit the model to the current display area.

This completes our tour of the Model view. Let’s next see how we can change the layout of our semantic model.

Modifying the layout

In the Model view, we have a default All tables layout, which was created for us automatically by Power BI.

To modify this layout, follow these steps:

1. Minimize the Data and Properties panes by clicking on the arrow icon in the pane headers (>).

2. Click on the tables and drag them closer together. Use the Fit to screen icon at the far right of the Footer to zoom in on the table layout. You should now be able to clearly see the table names and columns in the tables.

3. Move the Calendar and People tables next to one another in the top center of the Canvas by clicking on and then dragging and dropping. Place the Budgets and Forecasts and Hours tables underneath these two tables. Use the Fit to screen icon in the footer to zoom in on the tables. Note that we cannot see all of the columns in the Hours table or the Calendar table. A right-hand scroll bar is present on both tables.

4. Use the sizing handle in the bottom-right corner of the table to adjust the size so that we can see all of the columns in the table. When finished, your Canvas should look similar to this:

Figure 5.2 – Data tables in the model

Now that we have modified the layout such that we can easily see all tables on the screen, let’s continue building our semantic model by defining relationships between the individual tables.

Creating and understanding relationships

Now that we can clearly see our tables and columns, we can create relationships between our tables. Creating relationships between tables allows calculations and aggregations to work across tables so that multiple columns can be used from separate, related tables.

For example, once the People table and the Hours table are related to one another, we can use the ID column from the People table and the TotalHoursBilled column from the Hours table. By doing this, TotalHoursBilled aggregates correctly for each user Identifier (ID) in the People table.

To create this relationship between the People table and the Hours table, click on the ID column in the People table and drag and drop it onto the EmployeeID column in the Hours table. The New Relationship dialog is displayed, as follows:

Figure  – New relationship dialog

Click the Save button and note that a line appears on the canvas that connects the People table to the Hours table. This creates a relationship between the ID column in the People table and the EmployeeID column in the Hours table.

You can check the columns that are involved in a relationship by hovering your mouse over the relationship line. The line changes slightly and the columns involved in the relationship become shaded.

Figure  – Relationship between two tables

If you notice that ID and EmployeeID are not columns associated with the relationship, hover over the relationship line, right-click it, and choose Delete. Then, try again.

Note that the line has 1 next to the People table and * next to the Hours table. This means that this relationship is one-to-many or many-to-one.

In other words, there are unique row values in the People table that match multiple rows in the Hours table. This makes sense since each employee submits an Hours report for every day. The designation of 1 (unique) or * (many) defines the cardinality of the relationship between the tables.

There are actually four different cardinalities for relationships in Power BI, as outlined here:

• One-to-one: This means that there are unique values in each table.
• Many-to-one: This means that there are unique values in one table that match multiple rows in another table.
• One-to-many: This means that there are unique values in one table that match multiple rows in another table.
• Many-to-many: This means that neither table has unique values for rows. It is generally good practice to avoid these types of relationships because of their complexity and the amount of processing and resources required.

The designation of many-to-one versus one-to-many is simply a matter of which table is defined first within a relationship. In other words, the relationship between our People table and Hours table could be either many-to-one or one-to-many, depending on which table we defined first in our relationship.

Note that there is also an arrow icon in the middle of the line that points from People to Hours. This indicates that the People table filters the Hours table, but not vice versa. This is known as the cross-filter direction. Cross-filter directions can be either Single or Both, meaning that the filtering occurs only one way or bidirectionally. In simple semantic models, you generally don’t have to worry about cross-filter direction, but it can become very important if the complexity of the semantic model increases.

Finally, note that the line that forms the relationship between the tables is solid. A solid line indicates an active relationship. Inactive relationships can be created between tables, and these are represented as a dashed line. In Power BI, there can only be a single active path between tables.

As models become more complex, multiple paths from one table to another can be created. In those cases, one or more of the relationships or pathways become inactive. However, even though the relationship is inactive by default, it can be used within calculations that specify using specific relationships.

We can view and modify the relationship definition by double-clicking on the relationship line. This brings up the Edit relationship dialog, which looks nearly identical to the New relationship dialog shown in Figure 5.3.

Note that since the Hours table is defined first and the People table is defined second, the Cardinality value of our relationship is Many to one (*:1). Also, note that the relationship is active and that the Cross filter direction value is Single.

In many-to-one and one-to-many relationships, a Cross filter direction value of Single means that the one side of the relationship filters the many side. Finally, note that the EmployeeID column in the Hours table and the ID column in the People table are highlighted in gray. This shows us the columns that form a relationship between the two tables. Close the Edit relationship dialog by clicking the Cancel button.

Now, let’s create another relationship, this time between our Calendar table and the Hours table. To do this, we can use the Manage relationships functionality, as follows:

1. Click on the Home tab of the ribbon. Then, in the Relationships section, choose the Manage relationships button. The Manage relationships dialog is displayed, as shown in the following screenshot:

Figure 5.5 – Manage relationships dialog

Here, we can see our existing Active relationship between the Hours table and the People table, including the columns involved in the relationship in parentheses. From this dialog, we can Edit or Delete the relationship or have Power BI attempt to Autodetect relationships between tables. Power BI can sometimes autodetect relationships between tables based on the column names and row values.

2. Select the + New relationship button. This displays the New relationship dialog, as shown in the following screenshot:

Figure 5.6 – Create relationship dialog

3. In the first drop-down menu, choose the Hours table. A preview of the table will be displayed.

4. Click the Date column.

5. Choose Calendar in the second drop-down menu. A preview of this table is displayed.

6. Choose the Date column from this table. Power BI detects the appropriate Cardinality and Cross filter direction values. Since there are no other relationships between these tables, Power BI checks the Make this relationship active checkbox.

7. Click the Save button to create this relationship. Note that this new relationship now appears in the Manage relationships dialog.

8. Click the Close button to close the Manage relationships dialog. In the canvas, there will now be a relationship line linking the Calendar table to the Hours table.

Congratulations—you have successfully linked the three separate tables to create a semantic model!

For now, we won’t link the Budgets and Forecasts table within our semantic model. This is a good time to save your work.

Now that our semantic model is created, let’s explore our semantic model a little closer by creating some visuals.

Exploring the semantic model

Before we move on, let’s do some exploration of our data in order to understand our data, our semantic model, and how this data can ultimately be viewed by end users. To do this, follow these steps:

1. Start by clicking on the Report view in the Views bar.

2. At the bottom of the report canvas, click the plus (+) icon next to Page 1. This creates a new blank page, Page 2. Double-click Page 2 and rename it Utilization, and then press the Enter key. This changes the name of the page to Utilization.

3. Expand the People table in the Data pane by clicking the small arrow to the left of the People table. Check the box next to the Name field. This creates a Table visualization on our report canvas with the names of employees. Use the sizing handles for this visualization to resize the table to take up the entire page.

4. Expand the Hours table. Make sure that the table visual is selected and then check the box next to the Hours field. The number of hours reported by each employee is shown next to their name. This occurs because of the relationship between our People table and our Hours table. Because these tables are joined by a relationship based on the ID of each employee, we can use fields from both tables in visualizations. Due to this, the rows in the Hours tables are automatically filtered based on this relationship.

Note that when the Table visualization is selected, the Hours and People tables have small checkmarks next to them. This indicates that the visualization contains fields from these tables.

5. We know that the Category field of the hours is important, regardless of whether the hours are billable or not. With the table selected, click on the Matrix visualization in the Visualizations pane. This icon is to the immediate right of the highlighted Table visualization. Note that the Build visual pane changes from just displaying a Values field well to now containing field wells for Rows, Columns, and Values. Our Name field is now under Rows, while our Hours field is under Values and shows up as Sum of Hours.

6. From the Hours table, click on Category and drag and drop this field into the Columns field well. We can now see a breakdown of the hours for each employee by category.

It is now obvious that we can calculate a simple version of our utilization metric by taking the number of Billable hours for each employee and dividing them by the Total number of hours for each employee. Save your work before continuing.

Conclusion

Building a semantic model in Power BI is a critical step in transforming raw data into reliable, self-service analytics. By organizing tables, defining relationships, and understanding how filtering and cardinality work, you enable calculations and visuals to behave as expected across your reports. This foundation not only improves accuracy and performance but also makes your Power BI solutions easier to scale and maintain over time.

If you’d like to explore this topic in greater depth with guided examples, best practices, and real-world scenarios, you can learn more in Learn Microsoft Power BI - Third Edition by Greg Deckler, where semantic modeling and calculations are covered as part of a complete, end-to-end Power BI learning journey. This book helps you master self-service BI and become your team’s data hero or launch a new career. You’ll learn how to clean data, build models, and use advanced analytics in Power BI to unlock valuable insights and drive better business decisions.

Author Bio

Greg Deckler is a Vice President at a global consulting services firm. In addition, I am a 7-time Microsoft MVP for Data Platform. As an active member in the Columbus IT community, I founded the Columbus Azure ML and Power BI User Group (CAMLPUG) and have presented at many different conferences and events including Dog Food, SharePoint Saturday, CloudDevelop and M3. I am also the author of Microsoft Hates Greg's Quick Measures (MSHGQM) tool and the associated YouTube channels Microsoft Hates Greg and DAX For Humans.