Skip links

Calculated columns vs DAX Measures in Power BI: A comprehensive guide

Power BI is a powerful tool for data visualisation and analysis. Two key features that help you analyse and calculate your data are calculated columns and DAX Measures. But what is the difference between these two functions and when should you use which one? In this blog post, we will highlight the key differences between calculated columns and DAX measures in Power BI and show you how to use them effectively.

What are calculated columns?

Calculated columns are created at the data model level and stored in the underlying data table. These columns calculate static values for each row in the table and are particularly useful for creating new attributes or deriving additional information from existing data.

Important features of calculated columns:

  1. Data model level: Calculated columns are created and saved directly in the data model.
  2. Line level: The calculations refer to each individual line in the table.
  3. Static calculations: The values are calculated once and remain unchanged unless the data source is updated.
  4. Useful for additional attributes: Ideal for creating new fields based on existing data.
  5. Storage space: Calculated columns require storage space in the data model, which can increase the file size.

What are DAX Measures?

DAX Measures are dynamic calculations that are performed at runtime based on the current context of the report. They are not stored in the data table, but are calculated based on the filters and aggregation functions used in the report.

Important features of DAX Measures:

  1. Dynamic calculations: Measures are calculated at query time and adapt to the current report context.
  2. Dataset level: Measures can aggregate data across multiple tables or rows, depending on the visualisation context.
  3. Aggregation functions: Measures use functions such as SUM, AVERAGE, MIN, MAX and others to aggregate data dynamically.
  4. Analytical calculations: Suitable for complex calculations, KPIs and other analytical functions.
  5. No additional storage space: Measures do not take up any additional memory space in the data model as they are calculated at runtime.

When should you use calculated columns?

Calculated columns are ideal if you:

  • Require static values that do not change.
  • Create new attributes or dimensions based on existing data.
  • have to perform calculations at row level.

When should you use DAX Measures?

DAX Measures are ideal if you:

  • Require dynamic calculations that change based on the context of the report.
  • aggregate data, e.g. sum, average or other aggregations.
  • Create complex calculations and KPIs that change depending on filters and segmentation.

Examples for illustration

Example of a calculated column:

Let's say you have a table with sales data and want to create a column that shows the total sales per product.

Total sales = [sales price] * [quantity]

 

Example of a DAX measure:

Suppose you want to calculate the average sales per product category, which changes dynamically based on the filters applied in the report.

Average sales = AVERAGE(Sales[total sales])
 

Conclusion

To summarise, calculated columns and DAX Measures are both essential tools in Power BI, but they serve different purposes. Calculated columns provide static, row-based calculations that are stored in the data model, while DAX Measures provide dynamic, contextual calculations that are executed at runtime.

Kevin Schwarz is sitting on a staircase.
Contact us

Hi, I am Kevin - Power BI Consultant

Power BI enables it Company, their data in valuable Findings to transform. Through the right Implementation from calculated Columns and DAX Measures can You deeper Insights Win and well-founded Decisions meet.

info@novalutions.de

0221 - 42317679

Questions & Answers

Frequent Questions tostar 2Power BI calculated Columns & DAX Measures

Leave a comment