Tutorial 2: Data Modeling and Relationships in Power BI

Welcome back! In the first tutorial (introduction), we explored the basics of Power BI Desktop — importing data, transforming it, and building simple reports. In this second tutorial, we’ll go a step deeper by modeling data across multiple tables. You’ll learn how to establish relationships, build calculated columns and measures, and understand the structure behind professional Power BI dashboards.

The Set Up

We’ll be using Power BI’s built-in sample data. When you open Power BI Desktop, go to Home > Use sample data > Load sample data > Tick both Sheet1 and Financials from the Financial Sample.xlsx and then click Load.

Creating Relationships

Since we’re working on more than one dataset, in order to use both simultaneously, we should connect them through a relationship.

Open Model view (left-hand side panel).

You’ll see Sheet1 and Financials as two separate tables. Drag Country from Sheet1 to Country in Financials to create a relationship.

As you can see, there’s a problem because there are duplicate country, so cancel that, and go to Table View on the left-hand side panel. On the right side panel, Data, click on Sheet1 to get an overview of the dataset. From the drop down for Country column, we can see that there are only 5 unique countries.

And same thing for Financials.

Now go back to the Model View on the left-hand side panel again. And go to Home > Enter Data.

Double-click on the header to name it “Country”, input all 5 countries, name the table “CountryTable” and click Load.

Drag Country from CountryTable to Country in Sheet1. Then drag Country from CountryTable to Country in Financials. This creates a star schema where:

• CountryTable is a shared dimension
• Sheet1 and Financials are fact tables (or semi-facts)
• One-to-Many: From CountryTable → Sheet1 and Financials
• Cross-filter direction: Use Single for performance and clarity

Now the duplicate issue is resolved and we have a clean, scalable data model!

Adding Calculated Columns

Now if we want to add a new column to our dataset, we can do that by going to Table View on the left-hand side panel > Click on the three dots on the table you want to edit > New column.

And enter the following formula or what we call DAX (Data Analysis Expressions) on Power BI:

Profit Margin = ([Profit] / [Sales])

Then press Enter on your keyboard.

This column calculates the percentage of profit for each transaction.

To make the formatting more recognizable as a percentage, you can then click this % icon.

Try this yourself. Create category columns with this DAX:

Profit Category = IF([Profit] > 0, “Profit”, “Loss”)

Creating DAX Measures

Instead of adding too many columns, we can use measures for aggregations. Click the three dots on your selected table for more options, in our case it’s still the financials table > New measure.

Enter this DAX:

Total Sales = SUM(financials[Sales])

And we can see that it’s added to our table. It’s not added as a new column in our dataset though. But since it’s in our table, we can use it as a measure for that we can drag and drop in the Report View section.

We can also add other measures like:

Average Discount = AVERAGE(financials[Discounts])

And this:

Total Profit = SUM(financials[Profit])

Using Matrix Visuals and Tooltips

Now go back to Report view and try adding a Matrix visual.

Drag Country from Sheet1 to the Rows. And Total Sales, Total Profit (measures) from Financials to the Values. You might then get an error like this:

All we have to do is to go to the Table View, and fix the data type of the columns related to our DAX measure. Our Total Sales measure uses the Sales column, and our Total Profit measure uses the Profit column. So click on the header of those column, and you will be automatically on the Column tools menu, then just click the drop-down menu on Data type and select Decimal number instead of Text.

It should work now.

That’s it for this second tutorial!

See you on the next one!