When working with Data Analysis Expressions (DAX) in Power BI, mastering when to use a calculated column versus when to use a measure – a concept that is often misunderstood – is critical to reporting success. Choosing the incorrect one can result in a variety of issues, including poor report performance and incorrect results.
You can achieve faster reporting and (if your code is appropriate for the scenario) more accurate results by understanding how both calculated columns and measures are evaluated, the situations in which we would use one or the other, and structuring your code to take advantage of the strengths of the DAX language.
Numeric calculations in Data Analysis Expressions with Power BI | Community Webinars
In general, calculated columns and measures are used to perform arithmetic operations on top of the data in our model. For example, suppose you have a sales table with a Unit Price column and an Order Quantity column, and you want to know the Order Total for each line individually or in aggregate. Let’s take a look at some common business scenarios using the Power BI file.
Understanding Calculated Columns
When learning about calculated columns, it’s important to understand why evaluation context is important. The “environment” in which the formula is evaluated is referred to as the evaluation context. Although calculated columns can compute aggregate values, by default, the evaluation context is performed for each row, which makes calculated columns suitable for some but not all computations. If we want to know the Order Total for each line in the example above (Figure 1), which could represent an individual order from a customer, we should use a row-by-row calculation, which makes a calculated column a good choice.
Order Total = ‘Sales'[Unit Price] * ‘Sales'[Order Qty]
The data in the column is stored in the xVelocity in-memory database with calculated columns, which means that a calculation is performed before the model is queried by the user. Calculated columns are beneficial in terms of performance because they require less virtual memory as the user interacts with the report. Calculated columns, on the other hand, take up more space in your database. Other benefits of calculated columns include the ability to view values in the column and the use of the calculated column in a slicer or a measure to filter data according to certain criteria.
Assume we want to use our Sales table (included in the sample file) to categorise items as low, medium, or high-priced (Figure 2). To accomplish this, we can use the following DAX expression in a calculated column, which will allow us to add a slicer and see only the categories that we’re interested in, as shown below:
Cost Bucket =
IF ( ‘
Sales'[Unit Price] < 50,
IF ( ‘Sales'[Unit Price] < 100, “Medium”, “High” )
With the “High” Cost Bucket calculated column slicer selected, here’s an example:
Measures are commonly used to calculate aggregates, such as a column’s sum or average. Measures are computed at the time of your query, which means they aren’t saved in your database and are instead used to run a query at the time of your request. Measures are generally faster because they are not stored in memory, but it is important to understand the tradeoff between in-memory storage and processing power in situations where either a measure or a calculated column could be used.
Using the example above as an example, a DAX expression where a measure would be appropriate could be when you want to calculate the sales for all of your high-priced inventory (Figure 4). Within this measure, we’re using a calculated column called Cost Bucket. In DAX, “reusing” metrics in this way is considered best practise. If you changed the definitions of low, medium, and high-priced, these changes would be reflected in this metric. The expression for this measure would be as follows:
Sum of High Orders =
CALCULATE ( SUM ( ‘Sales'[Order Total] ), ‘Sales'[Cost Bucket] = “High” )
It’s also helpful to understand how Power BI evaluates measures. Using the example above, let’s break down the steps:
- Examine the context of the evaluation. The DAX engine is selecting high-priced inventory in the example above (Figure 2), or where ‘Sales'[Cost Bucket]=”High.”
- To the underlying table, apply the evaluation context. This means that Power BI is only filtering ‘Sales'[Order Total] for high-priced inventory in the example above (Figure 3).
- Complete the aggregation process. This means we’re summing the Order Total, or SUM(‘Sales'[Order Total]) in the example above.
- Please return the results. The measure, Sum of High Orders (Figure 4), matches the results when the rows of the calculated column, Order Total (Figure 3), in the table are totaled, as shown above.
Although there are additional technical and performance considerations when deciding whether to use a calculated column or a measure, the following are the key differences:
- When you want to evaluate each row, use a calculated column.
- When you need an aggregate, use a measure.
Check out Microsoft’s Power BI Documentation if you’re interested in learning more about measures or calculated columns. Contact us if you have any questions or would like to learn more about how Addend Analytics can assist your company with Power BI.
Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S. Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at email@example.com or Contact us.