- Ciao, friends. In this video, I want to
answer a very common question. What is the difference between a measure and a calculated column? Indeed, they're completely
different things but you need to understand
well the differences between the two. Let's look at the report. Here I have a report
that is slicing by brand and I already have a calculated column that computes the sales amount and a measure that
computes the sales amount. If I put the measure or
the calculated column, the result is totally identical. But why should I use a measure and when should I use a column? The main difference is
that calculated columns are computed once and stored in the table. So they are computed once
during process refresh and they are stored in the model. It means that calculated
columns use space in your model. The more calculated columns you have, the larger your model will become. Whereas measures are
computed at query time. A measure is stored in the
model only as source code but it is computed only when
it is used in the report. So line amount is stored in the model and it will not be computed
when projected in the report. Sales amount, on the other hand, will be computed every time. There is also another important difference between measures and calculated column. Measures are executed in the
filter context of the visual, calculated columns are
computed at process time when there is no filter context. So they cannot have access
to the filter context. When to use what? Well, as a general rule, you use measures whenever you can. And you only revert to calculated column if you need the physical structure
of the calculated column. In short, whatever you
want to see as a number in your report, you use a measure. Whenever you need to slice
and dice by a column, then you need to create a column because you cannot slice by a measure whereas you can slice by a column. Besides, it is important to
remember about the difference because you cannot use the
same expression for a measure or a column. Let's look at the measure first. I have my measure that computes
sales amount using SUMX over sales of quantity times net price. What happens if I use the same
code for a calculated column? Well, I can do that. I can just copy everything and create a new column where let's call it
sales amount as a column and I create a new column that contains the same expression
as the previous measure. I need a bit more space in order to show you all the numbers. Let's see what happens if I use the column and I put that in the report. The number is clearly wrong and it's not only a matter of formatting because if I go to the column and I format it as a decimal number, you see it's just a huge number. Why that? What happened? Well, I used the code of
the measure in a column. A measure is expected to work in the filter context in the report, whereas a column is evaluated outside of that filter context. If I look in my sales table to my column, let me go to the end, you see that for every
row of the sales table, that column contains the grand total, which is then later
aggregated by my report, which is summing for every row of the sales table the
amount of the column. If you write a column, you need to rely only on the row context of the current calculated column. The opposite is true. I can get rid of this column and I can try to build a new
measure using the same code that I have for the column. This is the expression
of a calculated column and if I use it in a new measure, that will generate a syntax error. If I click on new measure, let's call it line amount as a measure, and I just write sales
quantity times sales net price. You see it's underlined in
red because it's an error. By the way, let me stop for one second and give you a simple trick. A lot of times, it happens at least to me, that I don't remember if I am creating a measure or a column. If that happens to you, you can just look at the menu, which is open at that point. Since I am creating a measure, I see Measure Tools. If I was creating a column, I would have seen Column Tools. Anyway, let's go back to the topic. It's wrong and if I hit Enter, the error message says that a single value for column quantity cannot be determined. The reason is, a measure is executed in the filter context of the report but there is no row context. With no row context, I cannot evaluate the values of columns. That's why measures always have SUMX or other aggregators in order
to either create a row context or just aggregate values from a column. So you always need to choose
between a measure and a column. Measures are typically better
because they do not use space. Columns are useful
whenever you want to slice or you have very heavy calculation and therefore pre-computing results in a column is a better option. Enjoy DAX!
