What's the Difference Between Calculated Columns, Calculated Tables, and Measures?

Power BI provides report creators with a variety of methods for calculating and modeling data. Some commonly helpful tools include calculated columns, calculated tables, and measures.

If you are a newer Power BI user, you may be unsure which of these is the best choice for your needs. If you’ve been using Power BI for a while, you are probably familiar with these functions, but may be wondering if you are utilizing them optimally. Let’s define each one and explore its primary uses. 

Calculated Columns

A calculated column is a column of data that is added to an existing table in your model. This is done either in report view or data view using a DAX formula to determine the data that is displayed. The column will be shown under Field as usual, but you will notice a symbol indicating that a formula was used.

Calculated columns are like other columns in your report, including that they are stored in memory. When you refresh your report, the column will calculate row by row, so it cannot give you real-time updates.

When to use a calculated column

A calculated column can be a useful tool if you are looking to combine data from multiple columns in a table or across tables. You might also want to extract specific sub-data from existing columns.

To create a calculated column:

• Right-click on the table name
• Click “New Column”
• Enter your DAX formula
  
  

Calculated Tables

A calculated table differs from other tables you might have in your report because it is created by pulling from data that is already in your model, whereas most other tables are imported from an outside source. 

A calculated table can have a connection with other tables. You may choose to join two tables, or extract data from multiple tables to be displayed together. Like calculated columns, these tables are stored in memory and will update whenever the source data is updated.

When to use a calculated table

Calculated tables are great for data that you want to be stored in the model, as opposed to having real-time calculations. If you need to join two tables or combine data from multiple tables, this function will work well for you. Many report creators enjoy it for creating calendar tables as well.

You may also choose to use a calculated table for calculations that you do not want in your end report, or that will not be shared with others. This option is good for modeling different scenarios. Modelers often use calculated tables to debug a lengthy DAX expression to make sure it is running smoothly.

To create a calculated table:

• From Report View or Data View, go to the Modeling tab
• Within Modeling, find the section for Calculations
• Click “New Table”

If you create the table in Data View, you will be able to see it right away, so this can be a good starting point.

Measures

Measures are a very common function in Power BI. They are best used for aggregated data, like the oft-used SUM or SUMX. You can create a measure specific to your needs using a DAX formula. Power BI also has “quick measures” that are already in DAX, so check these to save yourself from writing the code. This is a great option if you are still learning DAX or have a common calculation need that is likely already represented in the quick measures.

Measures are calculated in real time, so they are not stored in memory. You can create a measure in Report View or Data View. When you create a measure, you will notice that it is listed under Fields with a calculator symbol next to it. This indicates that it is the result of a calculation, rather than pulled from another source. 

When to use a measure

Measures are most useful if you need to aggregate your data in some way, or need up-to-the-minute calculations. The other benefit of creating a measure is that you can then apply it to other tables as well - once it is created you can move it as needed. If you know there is a calculation you will be completing often, create a measure for ease of use in the future.

To create a measure:

• In Report view, go to the Modeling tab
• Click “New Measure”
• Enter your DAX formula
• When complete, your measure will be listed under Fields
  
  

PBRS from ChristianSteven Software

Once you have your reports optimized with the use of targeted calculations, you need to ensure they are getting into the right hands at the right time. ChristianSteven Software can take the pressure off with our automated reports scheduler for Power BI. Start your free trial of PBRS today to find out how you can maximize your Power BI usage.

Get Features PDFStart Free Trial