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 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 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.
For more information on calculated columns, including the use of IF statements, see Tutorial: Create calculated columns in Power BI Desktop.