- 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!