Since my school time in Temasek Polytechnic, I have been confused between the calculated columns and measures that available in Power BI. There was no distinguish example to explain them. I try my luck to search around and get a better understanding of these two fields.
The calculated columns and measures are one of the few concepts to learn and master in DAX.
What is DAX?
It stands for Data Analysis Expressions (DAX). It uses to perform basic calculation and solve data analysis problems. According to the Microsoft website, DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.
This may be similar to the Microsoft Excel formulas. With that knowledge, it will be useful for DAX understanding.
What is the measure?
According to the Microsoft website, it explains that the measures are used in some of the most common data analyses. Simple summarizations such as sums, averages, minimum, maximum and counts can be set through the Fields list well. The calculated results of measures are always changing in response to your interaction with your reports, allowing for fast and dynamic ad-hoc data exploration.
It is useful whenever you do not want to compute values for each row but, rather, you want to aggregate values from many rows in a table.
In Power BI Desktop, a measure is created in Report view or Data view. The new measures appear in the Fields list just like other fields, but it will have a “calculator” icon showing its values. You can name the measures, and add them to a visualization just like other fields.
Measures calculate a result from a DAX formula. An example of how to create a measure can be found in this link. It uses the DAX formula to sum the sales of the year, and calculate sales projections for the coming year. It is expected 6% increase in business.
Projected Sales = SUM('Sales'[Last Years Sales])*1.06
What is a calculated column?
With calculated columns, you can add new data to a table already in your model. But instead of querying and loading values into your new column from a data source, you create a DAX formula that defines the column’s values.
Unlike custom columns that are created as part of a query by using Add Custom Column in Query Editor, calculated columns that are created by using the New Column feature in Report view or Data view are based on data you’ve already loaded into the model.
For example, you might choose to concatenate values, do addition, or extract substrings from two different columns in two different but related tables. The calculated columns appear in the Fields list just like other fields, but it will have a different icon showing its values are the result of a formula. You can name the calculated columns, and add them to a visualization just like other fields.
An example on how to create a calculated field can be found in this link. It uses the DAX formula to create a concatenated string from two columns.
One important concept that you need to remember about calculated columns is that they are computed during the database processing and then stored in the model. In more technical explanation, in data models for DAX, all calculated columns occupy space in memory and are computed during table processing. If you familiar with SQL computed columns, they are computed at query time and do not use memory.
Therefore, when computing a complex calculated column, the time required to compute is always process time and not query time, resulting in a better user experience. However, you have to remember it uses our precious RAM.
The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report.
A measure operates on aggregations of data defined by the current context, that depends on the filter applied in the report, such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.