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