When you first dive into Power BI and start using DAX, you will run into both Calculated Column and Measure. At first glance, they can be quite similar but behave differently in how and when they are calculated. This blog will break down the differences between them and help you choose the right one when building visualizations in Power BI.
What is Calculated Column?
Calculated Columns simply allow you to add a new column to your table. It is computed row by row in the data model.
What’s “row by row”? – Power BI will look at every single row in the data model, ONE AT A TIME, and apply DAX formular for every row INDIVIDUALY.
The new column is stored in the data model, as the result, it will take up memory. Just like any other columns, the value of Calculated Column will not change under any applied filters, unless you refresh the data.
Here is a little example:
You have Product table below:
| Product ID | Product Name | Price | Sold Quantity |
| 01 | A | 1.99 | 5 |
| 02 | B | 2.99 | 10 |
Now you want to create a column that shows Sale values for each product.
You write a DAX formula: Sale = Product[Price] * Product[Sold Quantity]
Results:
| Product ID | Product Name | Price | Sold Quantity | Sale |
| 01 | A | 2 | 5 | 10 |
| 02 | B | 3 | 11 | 33 |
What is Measure?
A Measure is a dynamic calculation that is computed at query time.
Computed at query time? – When you add a Slicer or any sort of filters the visual, then the Measure’s values will change based on these filters. “At query time” means Power BI will wait for user selection and goes “I calculated the measure result based on User’s Filters”. Unlike Calculated Columns, you can change the value of a Measure based on what you have in the visuals and filters.
Measure typically performs aggregation like SUM, AVERAGE, COUNT, etc, and iterate through each
Another little example:
Now that you have updated the Sale table, you want to know the Total Sale Amount for all the products. Basically, you want to add up the Sale amount of every single product together.
| Product ID | Product Name | Price | Sold Quantity | Sale |
| 01 | A | 2 | 5 | 10 |
| 02 | B | 3 | 11 | 33 |
Here is what you write in DAX: Total Sales = SUM(Product[Sale])
Without any filters, Power BI shows: 10 + 33 = 43
A Quick Summary
A Calculated Column is:
- Static
- Computed for each row (understand row context)
- Unaffected by filters
- Store in model
- Affect performance if overuse
Use a Calculated Column when you want to add something new to your table.
A Measure is:
- Dynamic
- Computed at query time (have no row context)
- Affected by filters
- Not take up storage = nice and smooth!
Use a Measure when you for anything that changes depending on filters and you want to show it in the dashboard or visual.
Do not stress too much about this if you did not get it the first time. Try things out, do some little experiments. Over time, you should develop good understanding of which one to go for in which context.
