When dealing with calculations in Power BI, you will probably face with the choice between Calculated Column vs Measure. Therefore, it’s extremely important to understand differences between these two, since inadequate usage of either can produce various problems: from bad performance to incorrect results.
Both Calculated Columns and Measures are being created using DAX, but the context is what separates these two.
Calculated columns
Calculated column is used to extend data model in Power BI. It behaves like any “regular” column you already imported into your model – it can also be used to define a relationships with other tables. Value of calculated column is being evaluated row by row, which means that it references value for that specific row.
Values in calculated columns are being calculated during data model processing and they are stored in the Power BI data model, which means that they consume memory. That said, it differs from traditional SQL understanding of calculated (computed) columns, which are computed during the query time and don’t consume memory.
Here is an example on how to use calculated column. Let’s say that we want to analyze phone calls based on wait time. We can use following DAX expression to create calculated column:
Wait Time Bins =
IF(
Main[waitTime] < 20, "Low",
IF(Main[waitTime] < 100, "Average",
"High") )
And we will get following results:
So, evaluation context here is row by row and we are getting following results in Power BI when using our new calculated column:
Measures
When you want to calculate value for many rows in a table, using Measures is the way to go. Using our previous example, let’s introduce new value which calculates percentage of calls per wait time bin (“Low”,”Average” or “High”) compared to total number of calls. We can write measure like this:
Wait Time Bins Prcnt =
DIVIDE(
COUNT(Main[callid]),
calculate(
count(Main[callid]),
ALLSELECTED()
)
)
Results are being shown in the following illustration:
Here, values are being calculated on the fly and not being stored in the data model (only meta data is stored, but not “real” data).
When to use which?
At first glance, Calculated columns and Measures are very similar, but there are some significant differences and consequentially choosing when to use each of them is important decision. In a nutshell, calculated columns are evaluated row by row and therefore not depend on using filters in the report. On the other hand, measures are being evaluated in the context of filtering (slicers, chart filtering, etc.)
Simply said, you should consider using calculated colums in following scenarios:
- When you wish to put calculated values in the slicer, or as an axe in the chart visual
- Create categorizations of values (like in our example, when we created different categories based on numeric values for wait time)
On the other side, you should use measures in scenarios when values need to be adjusted based on user interaction with the report.
Last Updated on August 21, 2023 by Nikola
