When using DAX, it is either in calculated columns or in measures. At the beginning of learning Power BI, it felt like calculated columns and measures were similar.
Logically, if both exist, there must be a difference between them, but I couldn’t make sense of it.
Now that I’m comfortable with those concepts, I feel a duty to help anyone face the same struggles.
In this article, we will give you concrete examples of the difference between Power BI measures and calculated columns so that you’ll be able to know when to use each. If you’re ready, let’s get started!
Memory space
Calculated Columns use both Disk and RAM. They are stored physically (in a table), resulting in a larger model. On the other hand, measures are stored as a source code.
They take almost no space until they are used in a report, in which case the CPU will initiate the computation of the measure, and a table will be stored in the RAM.
Computation
Calculated columns are computed based on data that has already been loaded into your data model. Unless the data is refreshed, they are calculated only once.
Now, for measures, they are computed at query time when used in the report. If you change your slicers or the context, they will be calculated again.
Space VS Computation
If measures are large and complex, you might consider computing intermediate values in a calculated column. Because in this case, complex calculations at query time using measures might take time to execute and will consequently slow down your report.
So using calculated columns may result in a better user experience. In this case, I would rather sacrifice the space than sacrifice the user experience.
Context
Say you create a calculated column for the age of your users. If you grab that column and add it to your visual, even alone, you’ll see that the values are shown row by row. If you create a measure, for example, for the revenue, the value will be singular. Why is that?
Calculated columns rely only on the row context; when you write an expression in a calculated column, it is evaluated for each row of the table outside the filter context.
Calculated columns are “aware” of the notion of rows. On the other hand, measures rely only on the filter context and are calculated at the level of granularity they are applied.
Read also : Power BI Tutorials: Understanding Context in DAX
Slicers Creation & Categorization
Since measures are aggregations and have no row context, you cannot filter/slice by a measure. In this scenario, only calculated columns can be used.
For example, let’s say you want to separate the full name by first name and last name, we can’t use measures, and we have to create a calculated column.
Another scenario where you can only use a calculated column is if you want to categorize text or numbers. Columns with boolean, for example, are always created using calculated columns (e.g., column is_adult).
Are you having trouble making this kind of decision? Let the experts at DASH decide for you. Contact us today!
To summarize
The choice between using a Measure or a Calculated column depends on many factors, such as :
- The space it takes and the complexity of the computation.
- Whether you need the result in filters/slicers
- The nature of your data inside the column
- If your data is for categorization
When both options are possible, you should generally opt for measures.