WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI Data Modeling

Power BI Data Modeling

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 and this relationship helps us to connect with multiple data sources using one of the features of power bi known as data modeling.

What is Data Modeling in Power BI?

Often times, we get the data in multiple tables and connect these tables together, and creating a relationship between them is called “Data Modeling.” By creating a relationship between tables, we insist the Power BI know how these tables are connected to each other and create reports from different fields of different tables using Power BI Data Modeling. This is one of the key strengths of Power BI because it not necessarily demands all the data to 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

Create Data Modeling in Power BI

To create a relationship between data table in power bi, you need data tables to work on and I have below three tables to work with named “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 below link, which is used for this example.

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course
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 and create a “Zone-wise” sales summary table by choosing “Region Names” from “City Table” and “Sale” Value from “Sales Table.”

Power BI Data Modeling - Zone-wise (Add Values)

  • Our summary table shows the same value for all the regions, as shown below. This is 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 “Manager Table” and “Sales” value from “Sales Table.”

Power BI Data Modeling - City-wise Summary Table

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

  • 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 “City Table” in these two table’s common columns is “City Name,” so use this column to create a relationship between these two tables using power bi data modeling.

  • Now from “City Table,” drag and drop the “City Names” column to the “City” column in “Sales Table.” It creates a line between “City Table” and “Sales Table.”

Power BI Data Modeling - Relation Bet City & Sales Table

  • If you want to know on which column it 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 here 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 “City Table” and “Manager Table” not with “Sales Table.”

Relation Bet City & Manager Table

  • Now come back to the “Report” tab and see the result in initially created two tables. This time it recognizes the relationship between tables, and accordingly, it has displayed results against each city and region.

Report View (Result)

  • To see and manage the relationship between tables, you can click on “Manage Relationship” under the “Home” tab.

Manage Relationship (Home Tab)

  • This will open all the relationship lists. You can “Edit” “Delete” these relationships by clicking on respective buttons.

Manage Relationship Window

As you can see above, the first relationship list says, “City Table (City Names)” is connected to “Manager Table (City Names),” and the second list says “Sales Table (City)” is connected with “City Table (City Name).”

Note: Power BI dashboard file can also be downloaded from the link below, and the final output can be viewed.

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

Things to Remember

  • Power BI Data modeling 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 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 –

  • RANKX Power BI Function
  • Timeline Visual in Power BI
  • Use Power View in Excel
  • Power BI vs Tableau vs Qlikview
0 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI Data Modeling Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI Data Modeling Excel Template

New Year Offer - Power BI Training Certification (22+ Hours of Training) View More