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 strength 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 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 as “Sales Table, City Table, and Manager Table” respectively.
Copy and paste the data directly to Power BI or you can copy the data to excel file and then import to Power BI as Excel file reference. So you can download the excel workbook template from the below link which is used for this example.
I have directly uploaded the data to Power BI.
- Now open the “Report” view.
- As you can see below we have used “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 “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 still we 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 this two table’s common column 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 “City Names” column to “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 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 –
