Data Modeling is one among the employed to connect multiple data sources in BI tool employing a relationship. A relationship defines how data sources are connected with each other and you’ll create interesting data visualizations on multiple data sources.
With the modeling feature, you’ll build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.

Within the above image, see a common data model, which shows a relationship between two tables. Both tables are joined employing a column name “Id”.
Similarly, in Power BI, you set the relationship between two objects. To set the relationship, you have to drag a line between the common columns. You’ll also view the “Relationship” in a data model in Power BI.
To create data model in Power BI, you need to add all data sources in Power BI new report choice. To add a data source, go to the Get data choice. Then, select the data source you want to connect and click the Connect button.

Once you add a data source, it is presented on the right side bar. Within the following image, we have employed 2 xls file to import data – Customer and Product.

In Power BI on the left side of the screen, you have the following three tabs −
• Report
• Data
• Relationships

When you navigate to the Report tab, you’ll see a dashboard and a chart selected for data visualization. You’ll select different chart types as per your need. In our example, we have selected a Table type from available Visualizations.

When you go to the Data tab, you’ll see all the data as per the defined Relationship from the data sources.

Within the Relationship tab, you’ll see the relationship between data sources. When you add multiple data sources to Power BI visualization, the tool automatically tries to detect the relationship between the columns. When you navigate to the Relationship tab, you’ll view the relationship. You’ll also create a Relationship between the columns employing Create Relationships choice.

Page 4 Image 7 5 — Create Relationships choice

You’ll also add and remove relationships in data visualization. To remove a relationship, you have to right-click and select the “Delete” choice. To create a new “Relationship”, you just need to drag and drop the fields that you want to link between the data sources.

You’ll also use the Relationship view to hide a particular column within the report. To hide a column, right-click on the column name and select the “Hide in report view” choice.

Creating Calculated Columns

You’ll create calculated columns in Power BI by combining two or more elements of the existing data. You’ll also apply calculation on an existing column to define a new metric or combine two columns to create one new column.
You’ll even create a calculated column to establish a relationship between the tables and it can also be employed to setup a relationship between two tables.
To create a new calculated column, navigate to Data View tab on the left side of the screen and then click Modeling.

When you navigate to the Modeling tab, you’ll see a New Column choice at the top of the screen. This also opens the formula bar, where you’ll enter DAX formula to perform calculation. DAX- Data Analysis Expression is a powerful language also employed in Excel to perform calculations. You’ll also rename the column by changing the Column text within the formula bar.

Within the following example, let us create a new column: Product Code (Product_C), which is derived from the last three characters of Prod_Id column. Then, write the following formula −
Product_C = RIGHT( Sheet1[Prod_Id],3)

A long list of formulas is also provided that you’ll use for creating calculated columns. You have to enter the first character of formula to be employed in calculations as shown within the following screenshot.

Creating Calculated Tables

You’ll also create a new calculated table in data modeling in Power BI. To create a new table, navigate to the Data View tab on the left side of the screen, and then go to the Modeling choice at the top of the screen.

DAX expression is employed to create the new table. You have to enter the name of a new table on the left side of the equal sign and DAX formula to perform the calculation to form that table on the right. When the calculation is complete, the new table appears within the Fields pane in your model.
Within the following example, let us define a new table – Table_CustC that returns a one column table containing unique values in a column in another table.

A new table is added under the “Fields” section in Power BI screen as shown within the following screenshot. Once the calculated column and calculated tables are created as per your requirement, you’ll use the fields within the Report tab in Power BI.
To add these objects, you have to select a checkbox and a relationship is automatically detected if possible. If not, then you’ll drag the columns that you want to connect.

To view the report, you navigate to the Report tab and you’ll see both “Calculated columns” and fields from the new “Calculated table” within the report view.

Managing Time-Based Data

Power BI allows to drill through time-based data by default. When you add a date field in your analysis and enable drill on your data visualization, it takes you to the subsequent level of time-based data.
Let us consider we have added Time-based table in Power BI visualization. We have added Revenue and Year column in our report.
Page 10 Image 18

Page 10 Image 19 — Managing Time-Based Data

We can enable the drill feature in visualizations employing the choice at the top. Once we enable the drill feature and click the bars or lines within the chart, it drills down to the subsequent level of time hierarchy. Example: Years → Quarters → Months.
We can also use Go to the subsequent level within the hierarchy choice to perform a Drill.

So, this brings us to the end of blog. This Tecklearn ‘Data Modelling in Power BI’ blog helps you with commonly asked questions if you are looking out for a job in Power BI. If you wish to learn Power BI and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI

• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources

Power BI Workflow

• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships

Power Query for Data Transformation

• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations

Filters and Drill Down Report

• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report

Power Pivot for Data Modelling

• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions

Data Analysis Expression – DAX Queries

• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features

Power BI Desktop & Administration

• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters

Introduction to Power Q & A

• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

838