Create calculated columns – Model the data

2023-06-03
No Comments

Create calculated columns

Calculated columns are columns you create by using DAX. Similar to calculated tables, calculated columns can only use the data already loaded into the model or new data generated by DAX, and they don’t appear in Power Query Editor because they are generated after the data has been loaded into the model. By nature, creating calculated columns widens your table, and they are calculated after all your data is loaded, so multiple calculated columns can contribute to slow performance of your data model.

If you’re experienced in Excel, creating calculated columns in DAX may remind you of creating columns in Excel, because DAX resembles the Excel formula language, and there are many functions that appear in DAX and Excel. There are some important differences, however:

  • In DAX, there is no concept of a cell. If you need to get a value from a table, you have to filter a specific column down to that value.
  • DAX is strongly typed; it’s not possible to mix values of different data types in the same column.

In general, calculated columns are especially useful when you are:

  • Creating columns to be used as filters or categories in visuals
  • Precalculating poorly performing measures

Here’s one way to create a calculated column:

Go to the Data view.

In the Fields pane, right-click a table where you want to create a calculated column.

Select New column.

Enter a calculated column expression by using DAX.

Press Enter.

After you complete these steps, you’ll be able to see the results immediately. The formula that you write is automatically applied to each row in the new column. You can reference another column from the same table in the following way:

‘Table name'[Column name]

Though it’s possible to reference a column within the same table by only using the column name, it’s not considered a good practice and should be avoided.

For example, in Wide World Importers, you can calculate total cost in a calculated column in the Sale table by using the following expression:

Click here to view code image

Total Cost = Sale[Total Excluding Tax] – Sale[Profit]

If you want to reference a column from a related table that is on the one side of a relationship, you can use the RELATED function. For instance, in Wide World Importers, you can add a calculated column to the Sale table to calculate the price difference between the standard unit price and the price a product was sold for:

Click here to view code image

Unit Price Difference = RELATED(‘Stock Item'[Unit Price]) – Sale[Unit Price]

Note RELATED and Inactive Relationships

By default, RELATED uses the active relationship. Though it’s possible to make RELATED use an inactive relationship, it’s much better to use the LOOKUPVALUE function for this. For more information on the function, see “LOOKUPVALUE” at https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax.

RELATED works on the many side of a relationship. If you want to add a column to the one side of a relationship and reference the related rows, you can use the RELATEDTABLE function. For instance, you can add a calculated column to the Customer table to count the number of related rows in the Sale table for each customer:

Click here to view code image

Sales Rows = COUNTROWS(RELATEDTABLE(Sale))

 Exam Tip

Unless you want to use the values generated from the calculated column as filters or categories in visuals, you should be creating measures, which we cover in Skill 2.3: Create model calculations by using DAX.

Need More Review? Create Calculated Columns

For more examples and a tutorial on how to create calculated columns, see “Tutorial: Create calculated columns in Power BI Desktop” at https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns.