What is Data Modeling in Power BI?

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

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.

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.

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 to create a “Zone-wise” sales summary table by choosing “Region Names” from “CityTable” and “Sales” Value from the “Sales” table.

  • 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.

  • 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.

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.

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.”

  • 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.

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.”

  • 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.

  • You may click “Manage Relationship” under the “Home” tab to see and manage the relationship between tables.

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

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.

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.

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: –

  • RANKX Power BI FunctionTimeline Visual in Power BIUse Power View in ExcelPower BI vs Tableau vs Qlikview