Chapter 5: Creating Data Models and Calculations

Even though we have loaded in some data, having raw data loaded into separate tables is not enough to enable analysis and visualization. In order to facilitate analysis and visualization, we must create a data model by building relationships between the individual tables, as well as calculations that will aid us in our analysis. Building a data model with relationships between tables and required calculations allows us to create the necessary reports that will prove useful and insightful to the business. In this chapter, we will learn how to create a data model, create Data Analysis Expressions (DAX) calculated columns, and understand the measures and techniques for troubleshooting DAX calculations.

The following topics will be covered in this chapter:

• Creating a data model
• Creating calculations
• Checking and troubleshooting calculations

Technical requirements

You will need the following in order to successfully complete the instructions provided in this chapter:

• An internet connection
• Microsoft Power BI Desktop
• LearnPowerBI_CH5Start.pbix downloaded from GitHub at https://github.com/PacktPublishing/Learn-Power-BI-second-edition/tree/main/Chapter05
• Check out the following video to see the Code in Action: https://bit.ly/3o92RrD

Creating a data model

The concept of a data model or dataset is fundamental to Power BI. In short, a data 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 data model is stored within an Analysis Services tabular cube. It is the creation of this data 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 data model. We will now stitch those individual tables, along with our previously created data table, into a cohesive data model that can be used for further analysis.

Touring the Model view

...

Creating calculations

We now have a workable data model that contains all of the raw data we will need to report on utilization. Recall that, at the beginning of Chapter 4, Connecting to and Transforming Data, with the rollout of unlimited Paid Time Off (PTO), it is imperative that employee utilization be tracked closely.

Utilization in this context is a calculation that involves the ratio of billable time versus the total time (billable and non-billable). From experience, the organization knows that, in order to remain profitable, the target utilization must be 80%. Therefore, we must create this calculation.

Calculated columns

Calculated columns are additional columns that are created in data model tables through the use of the DAX formula language. Calculated columns are computed for each row in a table at the time of their creation, as well as during the refresh process. Hence, the data refresh process executes the queries defined in Power Query Editor. These queries create...

Checking and troubleshooting calculations

Now that we have our calculations seemingly correct, it is important to take a closer look so that we can discover any anomalies or instances that might throw off the calculations.

Boundary cases

A common method of performing checks is to look at boundary cases, or the maximums and minimums of our calculations. To do this, follow these steps:

1. Start by clicking on the % Utilization column header in our table visualization. This sorts the table in descending fashion, where our highest % Utilization number is at the top of the list. We can see what some anomalies of very high utilization might be, such as % Utilization at the top, which is 175.00% for Cole, Camille.
2. To check this number, click on the Data view in the Views bar.
3. Click on the Hours table in the Fields pane.
4. Find the EmployeeID field in the Hours table and click on the drop-down arrow in the column header.
5. Uncheck the (Select all) checkbox to unselect...

Summary

In this chapter, we took a tour of the Model view of Power BI and learned how to build a data model by creating relationships between separate tables. In doing so, we learned about the different types of relationships, as well as the concept of cross filter direction. Next, we explored the data model we created to understand how we can use fields from different tables in the same visualization to gain insights into our data. Then, we created calculations to fulfill our goal of reporting on utilization as well as building relationships between tables. First, we created utilization calculations using calculated columns and began to understand the limitations of calculated columns and when they should and should not be used. Then, we created utilization calculations using measures in order to enable truly dynamic calculations that respond to user interaction.

Finally, we troubleshot our measure calculations by using a variety of techniques such as boundary condition checking...

Questions

• As an activity, try to answer the following questions on your own:
• What are the seven major areas of the Model view in Power BI?
• What are the four different types of relationship cardinalities that Power BI supports?
• What is cross filter direction and what are the two types of cross filter direction supported by Power BI?
• What are the two ways to create relationships in Power BI?
• What are calculated columns?
• When are calculated columns calculated?
• What are measures?
• When are measures calculated?
• What is the measure totals problem?
• What are three ways of troubleshooting calculations?

Further reading

To learn more about the topics that were covered in this chapter, please take a look at the following references:

• Create and manage relationships in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
• Work with Data view in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-data-view
• Create calculated columns in Power BI Desktop:
• Create measures for data analysis in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-measures
• Apply many-many relationships in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships