Power BI Data Modeling

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Before understanding data modeling, we need to understand how the relationship works in Power BI, using a relationship we use to access data from other data sources. This relationship helps us connect with multiple data sources using one of the features of power bi known as data modeling.

What is Data Modeling in Power BI?

We often get the data in multiple tables and connect them, and creating a relationship between them is called “Data Modeling.” By creating a relationship between tables, we insist that Power BI know how these tables are connected and create reports from different fields of different tables using Power BI data modeling. It is one of Power BI’s key strengths because it does not necessarily demand all the data be in a single table. Rather, you can have data in different tables and define a relationship between those tables with a common column.

Power-BI-Data-Modeling

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Data Modeling (wallstreetmojo.com)

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

Create Data Modeling in Power BI

To create a relationship between data tableData TableA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more in Power BI, you need Data Tables to work on. We have below three tables to work with: “Sales Table,” “City Table,” and “Manager Table,” respectively.

Power BI Data Modeling - Excel Data

Copy and paste the data directly to Power BI, or you can copy the data to an excel file and then import it to Power BI as an Excel file reference. So, you can download the Excel workbook template from the link below, which is used for this example.

You can download this Power BI Data Modeling Excel Template here – Power BI Data Modeling Excel Template

I have directly uploaded the data to Power BI.

Power BI Data Modeling - Data Tab
  • Now, open the “Report” view.
Power BI RANKX - Report View
  • As you can see below, we have used the “Table” visual.
Power BI Data Modeling - Insert Table
  • Try to create a “Zone-wise” sales summary table by choosing “Region Names” from “CityTable” and “Sales” Value from the “Sales” table.
Power BI Data Modeling - Zone-wise (Add Values)
  • Our summary table shows the same value for all the regions, as shown below, because we have used two different columns from two different tables.
Power BI Data Modeling - Zone-wise Table Values
  • Similarly, create one more table visualization to create a “City-wise” summary table. This time use “City Names” from the “Manager Table” and “Sales” value from the “Sales” table.
Power BI Data Modeling - City-wise Summary Table

Even though the “City Names” are in both tables, we still get the same numbers because Power BI cannot recognize the relationship between these three tables.

  • Now, come back to the “Relationship” tab. As you can see, we have three tables with their respective names.
Power BI Data Modeling - Model View

First, look at the “Sales_Table” and “CityTable” in these two tables’ common column is “City Name.” So, use this column to create a relationship between these two tables using Power BI data modeling.

  • Now, from “CityTable,” drag and drop the “City Names” column to the “City” column in “Sales_Table.” It creates a line between “CityTable” and “Sales_Table.”
Power BI Data Modeling - Relation Bet City & Sales Table
  • If you want to know which column has created the relationship between these two tables, then place the cursor on the connector line between these two lines. It will highlight two common columns in respective tables.
Power BI Data Modeling - Hightlight Relation Bet City & Sales Table

Here you go, it says the relationship between two columns in these two tables is “City.”

  • Now, we have one more table, i.e., “Manager_Table.” This table includes the city-wise manager list. The city is there in the other two tables as well, but this time, we will create a relationship between “CityTable” and “Manager_Table,” not with “Sales_Table.”
Relation Bet City & Manager Table
  • Now, return to the “Report” tab and see the result in the two initially created tables. This time it recognizes the relationship between tables. Accordingly, it has displayed results against each city and region.
Report View (Result)
  • You may click “Manage Relationship” under the “Home” tab to see and manage the relationship between tables.
Manage Relationship (Home Tab)
  • It will open all the relationship lists. You can “Edit” or “Delete” these relationships by clicking on the respective buttons.
Manage Relationship Window

As you can see above, the first relationship list says, “CityTable (City Names)” is connected to “Manager_Table (City Names),” and the second list says “Sales_Table (City)” is connected with “City_Table (City Names).”

Note: We can also download the Power BI dashboard file from the link below. We can view the final output.

You can download this Power BI Data Modeling Template here – Power BI Data Modeling Template

Things to Remember

  • Power BI data modeling is the process of creating a relationship between common columns of multiple tables.
  • If the column headings are the same across tables, then Power BI auto-detects the relationship between tables.
  • Using these columns, we can merge the tables as well.

Recommended Articles

This article has been a guide to Power BI Data Modeling. Here, we create a relationship between tables using data modeling to get Power BI reports from different fields of different tables. You can learn more about Power BI from the following articles: –