measures are perhaps the most powerful
element of power bi in this video we
will create our first measure and
contrast that with creating calculated
columns keep watching so great so how do
we define a measure to calculate this in
here our total sales amount was just
sales amount plus sales tax amount well
great I can define a measure which did
that can't I
well let's first of all let's try and
you measure this time and we're going to
say oh this is my total sales
oops alright total sales equals and you
know what I'm just going to be lazy I'm
going to copy that formula so I'm going
to go here sales amount what sales tax
amount click new measure again I'll say
total sales equals that right that's it
or is it Wow not really so if I hit
enter it gives me an error what happened
here well the same formula worked for my
column but not for my measure and gosh
this is one of the key principles here
which is called row contest not a row
context sounds kind of crazy and later
you will hear another sort of filter
context and that frankly row context and
filter context these four words scared
the out of me for a long time till
I realize that they're not really that
complicated so what a row context is is
the knowledge of a current row that you
have a current row so in here when I'm
defining a column and the formula is
total sales amount equal to
let's go down where it slightly variable
there you go so you now you see it
so we'll sear them out when I define it
as is sales sales amount plus the sales
tax amount right and let's say if I'm
trying to calculate this sell trial
again I'm trying to calculate this cell
so when it's trying to calculate this
cell and it goes to this formula the row
is implied it does know which row it is
on and like yeah I know
I'm on this row so when you say sales
amount I know exactly what you want to
grab this guy and when you say tax
amount I know exactly which one grab
this guy so there's no ambiguity right
so in a calculated column the row
context which is simply the knowledge of
the current row is implied when it's
trying to calculate a specific value in
that column it says yup I know we throw
them on I'm on this row and of course
the whole thing repeats for each one of
these cells so that's it
that's what real context is believe me
we'll talk a whole lot more about this
but for now it's really easy let's not
complicate things more than we need to
shall we
right so row context is simply a
knowledge of the current row and when
you're doing a calculated column it's
going you know step by step row by row
calculating each of these numbers it
obviously knows which row it's on
however when you define a measure
measures are not tied to a column
measures on overall so if in a measure
you say you know go to sales amount and
go to tax amount it's asking you which
one which one are we I don't know which
one you're talking about I have so many
I don't know how to choose and this is
what we call a naked reference right so
a naked column reference where it
doesn't have well any clothes on or in
other words it doesn't have any wrappers
around what wrappers am I talking about
I'm talking about
something that aggregates things
aggregation functions typically some
that's the simplest one right so so you
can't refer to columns directly because
there is no row context in measures
implicitly right by default there's no
row so if you refer to call them
directly it's saying I don't know what
you're talking about I don't know which
one which sales amount should I grab
help me out so it's not going to work
that way so we have to define measure
this measure slightly differently we're
just going to have to put some clothes
on it and we're going to wrap it in a
sum and now it's not a naked column
reference now it's say okay great I'm
just gonna sum everything up doesn't
matter which row what row this is going
to be a sum of everything alright hit
enter and it's there now you don't see
it in the table but that's the whole
idea that it's not manifested in the
table so right so we define a column now
we define a measure the same thing and
I'm going to hit save again go back to
our file and our size and notice the
size I'm going to hit refresh and known
it's nothing's happening it's still
three seven eight nine kilobytes with a
new measure and that my friends is one
of the biggest differences between a
calculated column and a measure
calculated column is actually physically
manifested in the table and saved back
to the file it's almost as if you had
pulled that column from your source data
so when you keep defining calculation
calculate count one after the other keep
building your calculations that way
guess what your you know your file size
is going to keep growing bigger and
bigger
and this is again a very small sample
data set
once you're working with realistic data
that can be a real problem
why why are we I don't care can be a
fifty gigabyte file who cares really
well yeah you should care because it's
not really about file size right so disk
space is plenty even a very basic laptop
you can pick up out of Costco is
probably going to have a terabyte of
disk space but these models don't quite
work this way so this is based on
tabular technology and one of the key
features of tabular technology is that
it's in memory yes it's in memory what
it does is that whole model is is first
of all its compressed to a crazy degree
we've seen that already right it started
off with 20 Meg's and compressed to a
really small size is compressed by great
amount but then it loads the whole thing
in memory and that's what makes it so
blazingly fast which is awesome but
guess what your memory you probably
don't have a terabyte of memory on your
laptop right so memory is more limited
than disk space so we want to be careful
about our file size because that's also
an indicator offer memory footprint
all right gosh so hey I feel very
passionately about it and I can go on
and on but I hope you get a clue the
message I'm trying to tell you is that
calculated column and measures are very
different and really measures is the way
to go
any numeric calculation you should not
be doing using calculated columns you
should be defining measures all right
even though the end result they look the
same they look for you the same now that
I have defined so let's give the get rid
of this guy
and if you bring in let's keep simple by
product key I want to do product
category subcategory perfect like I just
use the same now if I bring in my
calculated column total sales amount and
my measure total sales fix the
formatting of the sky perfectly notice
that they show the exact same result but
don't let that fool you because I have
shown you exactly what's happening under
the covers when I don't go here right so
calculated columns saved back to the
file measure not save this file that's
one difference there are other
differences between calculated columns
and measures one of that is measure
since it's never stored in the file it's
always calculated on the fly it's
dynamic that means it can it can reflect
a lot of changes that go in the report
and the model versus versus a calculated
column which cannot actually that's a
deeper point so I'm going to come back
to that later but for now I hope it's
clear to you that measures is the way to
go almost always we'll talk about
exceptions but for now I want you to
make that you have it you know people
are like left-handed or right-handed and
if you're in a shootout and you in a
duel you can just you know reach with
your dominant hand
I want measures to become your dominant
hand alright I rest my case
hey keep watching more videos and keep
learning power bi but if you did enjoy
this video I would love to hear from you
so leave a comment like subscribe all
the good stuff while we're on my friends
you