Model data in Power BI III

Introduction to modeling your data

Often, you connect to multiple data sources to create your reports. All that data needs to work together to create a cohesive(Exhibiting) report. Modeling is how to get your connected data ready for use.

Tasks in this module:

Create relationships between your data sources

Create a new field with calculated columns

Optimize data by hiding fields and sorting visualization data

Create a measure to perform calculations on your data

Use a calculated table to create a relationship between two tables

Format time-based data so that you can drill down for more details

 

Video: Overview of modeling data

In Power BI, you can create a relationship to create a logical(connection between similarities) connection between different data sources. A relationship enables Power BI to connect tables to one another so that you can create visuals and reports. This module describes data-centric(staring point) relationships and how to create relationships when none exists.

How to manage your data relationships

The Model view in Power BI Desktop allows you to visually set the relationship between tables or elements. A relationship is where two or more tables are linked together because they contain related data. Using relationships allows users to run queries for related data across multiple tables. Use the Model view to see a diagrammatic view of your data.

Tasks in this unit include: 

In the Model view, notice that a block represents each table and the lines between them represent relationships.

Adding and removing relationships is straightforward. To remove a relationship, right-click the relationship and select Delete. To create a relationship, drag the field from one table and drop the field on the field of the other table that you want to link.

 

To hide a table or individual column from your report, right-click the table or column in the Model view and select Hide in report view.

 

For a more detailed view of your data relationships, on the Home tab, select Manage Relationships. The Manage Relationships dialog box displays your relationships as a list instead of as a visual diagram. From the dialog box, you can select Autodetect to find relationships in new or updated data. Select Edit to manually edit your relationships. You can find advanced options in the Edit section to set the Cardinality(a measure of the number of elements of the set)and Cross-filter direction of your relationships.

 

Your options for Cardinality are explained in the following table.

Expand table

Cardinality options	Example
Many to One	The most common default relationship. The column in one table can have more than one instance of a value. The related table (or lookup table) has only one instance of a value.
One to One	The column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

Generally, we recommend minimizing the use of bi-directional relationships. They can negatively affect model query performance, and possibly deliver confusing experiences for your report users.

Setting accurate relationships between your data allows you to create complex calculations across multiple data elements.

Create calculated columns

Sometimes, the data that you're analyzing doesn't contain a field that you need. The answer might be calculated columns. You can create a new calculated column by transforming two or more elements of existing data. For example, you can create a new column by combining two columns into one.

Tasks in this unit include: 

One reason for creating a calculated column is to establish a relationship between tables when no unique fields exist. The lack of a relationship becomes obvious when you create a simple table visual in Power BI Desktop and get the same value for all entries.

 

For example, to create a relationship with unique fields in data, you can create a new calculated column for "CountryZip" by combining the values from the Country and the Zip columns.

To create a calculated column, select the Table view in Power BI Desktop from the left side of the report canvas.

 

From the Table tools tab, select New Column to enable the formula bar. You can enter calculations by using Data Analysis Expressions (DAX) language. DAX is a powerful formula language that lets you build robust(strong) calculations. As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.

The Power BI formula bar suggests specific DAX(deutscher aktienindex) functions and related data columns as you enter your expression.

 

After you create the new CountryZip calculated column in the Geography table and the Sales table, they can be used as a unique key to establish a relationship between the two tables. By going to the Relationship view, you can then drag the CountryZip field from the Sales table to the Geography table to create the relationship.

 

If you go to the Report view (select the Report view icon to go to the report view), you see a different value for each district.

 

Optimize data models

Imported data often contains fields that you don't need for your reporting and visualization tasks. Data might contain unnecessary information or it might be available in another column. Power BI Desktop has tools to optimize your data and make it more usable for building reports and visuals.

Tasks in this module include: 

Hide fields

To hide a field in the Data pane of Power BI Desktop, right-click the column and select Hide. Your hidden fields aren't deleted. If you used a hidden field in existing visuals, the data is still there; the hidden field just isn't displayed on the Data pane.

 

If you view tables in the Model view, hidden fields appear with an icon of an eye with a diagonal slash through it. The data in these tables is still available and is still part of the model. You can unhide any field that's hidden by selecting the icon. When you unhide the field, the eye icon is next to the field, without the slash through it.

Sort visualization data by another field

The Sort by Column tool, available on the Column tools tab, is useful to help ensure that your data is displayed in the order that you intended.

 

As a common example, data that includes the name of the month is sorted alphabetically by default, for example, August would appear before February.

 

In this case, selecting the MonthName field in the Fields list, selecting Sort by Column from the Column tools tab, and then choosing a field to sort by can remedy the problem. The MonthNo category sort option orders the months as intended.

 

Setting the data type for a field is another way to optimize your information so that you can manage the data properly. To change a data type from the report canvas, select the column in the Data pane, and then use the Format drop-down menu on the Column tools tab to select one of the formatting options. Any visuals you created that display that field are updated automatically.

Create measures

In Power BI, measures are defined calculations on your data that are performed at the time of your query. Measures are calculated as you interact with your reports and aren't stored in your database.

Tasks in this unit include: 

Create a measure

To create a measure, in Report view, select New Measure from the Modeling tab.

 

From the Formula bar, you can enter the DAX expression that defines your measure. As you enter your calculation, Power BI suggests relevant DAX functions and data fields. You also receive a tooltip that explains some of the syntax and function parameters.

 

To check the new measure works, make sure you set the Date field in the Date table to type Date. If your calculation is long, you can add extra line breaks in the Expression Editor by typing ALT-Enter.

 

Apply a measure

After you create a new measure, it will appear in one of the tables on the Data pane, which is found on the right side of the screen. Power BI inserts the new measure into whichever table you currently selected. While it doesn't matter, exactly, where the measure is located in your data, you can easily move it by selecting the measure and using the Home Table drop-down menu on the Measure tools tab.

 

You can use a measure like any other table column: just drag and drop it onto the report canvas or visualization fields. Measures also integrate seamlessly with slicers, segmenting your data on the fly, which means that you can define a measure once and then use it in many different visualizations.

Create calculated tables

Calculated tables are a function within DAX. Most of the time, you can import data into your model from an external data source. However, calculated tables provide intermediate calculations and data that you want stored as part of the model rather than as part of a query. You can use calculated tables, for example, to cross join two tables.

Tasks in this unit include: 

To create a calculated table, go to Table view in Power BI Desktop, on the left side of the report canvas. Select New Table from the Table tools tab to open the formula bar.

Type the name of your new table, the equal sign, and the calculation that you want to use to form the table. Your new table appears on the Data pane in your model.

 

After the new table is created, you can use your calculated table as you would any other table in relationships, formulas, and reports.

Explore time-based data

Analyzing time-based data with Power BI is a simple process. The modeling tools in Power BI Desktop automatically generate fields that let you drill down through time periods.

Tasks in this unit include: 

When you create a table visualization in your report by using a date field, Power BI Desktop automatically includes breakdowns by time period. For example, the single date field in the Date table was automatically separated into Year, Quarter, Month, and Day by Power BI.

 

Visuals display data at the year level by default, but you can change that by turning on Drill Down in the top, right-hand corner of the visual.

 

When you select the bars or lines in your chart, the system will drill down to the next level of time hierarchy, for example, from years to quarters. You can continue to drill down until you reach the most granular level of the hierarchy: days. To move back up through the time hierarchy, select Drill Up in the top, left-hand corner of the visual.

 

You can also drill down through all the data that is shown on the visual instead of through one selected period. To do so, use the Go to the next level in the hierarchy double-arrow icon.

 

As long as your model has a date field, Power BI automatically generates different views for different time hierarchies.