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.
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.
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.
4.5 (521 ratings) 6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
I have directly uploaded the data to Power BI.
- Now open the “Report” view.
- As you can see below, we have used the “Table” visual.
- Try and create a “Zone-wise” sales summary table by choosing “Region Names” from “City Table” and “Sale” Value from “Sales Table.”
- 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.
- 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.”
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.
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.”
- 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.
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.”
- 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.
- To see and manage the relationship between tables, you can click on “Manage Relationship” under the “Home” tab.
- This will open all the relationship lists. You can “Edit” “Delete” these relationships by clicking on respective buttons.
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.
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
- 6 Online Courses
- 29+ Hours
- Verifiable Certificate of Completion
- Lifetime Access
