fbpx

Create calculated columns

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 module include:

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-01

Video: Create calculated columns

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.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-02

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 Data view in Power BI Desktop from the left side of the report canvas.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-03

From the Modeling 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, also found in Excel, that lets you build robust 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 will suggest specific DAX functions and related data columns as you enter your expression.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-04

After you have created the calculated columns in each table, they can be used as a unique key to establish a relationship between them. By going to the Relationship view, you can then drag the field from one table to the other to create the relationship.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-06

For more information on calculated columns, including the use of IF statements, see Tutorial: Create calculated columns in Power BI Desktop

Optimize data models

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:

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-01

Video: Optimize data models

Hide fields

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

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-02

If you view tables in the Model view, hidden fields appear dimmed. The data in these tables is still available and is still part of the model. You can unhide any field that has been hidden by right-clicking the field and then selecting unhide.

Sort visualization data by another field

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

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-03

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

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-04

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

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-05

Setting the data type for a field is another way to optimize your information so that it’s handled correctly. To change a data type from the report canvas, select the column in the Fields pane, and then use the Format drop-down menu to select one of the formatting options. Any visuals you’ve created that display that field are updated automatically.

For more information, see Sort by column in Power BI Desktop

"The purpose of a business is to create a customer who creates customers" — Shiv Singh

Our reference list:

Liked our service? Start a project with us.