in this video we'll show you how power
bi relationships can replace an army of
Excel vlookup s-- keep watching now
that's perfect but we have a whole
product table we have this product table
with all these rich attributes that we
talked about so we want to be able to
slice and dice the sales amount by all
these attributes as well can we do that
let's give that a try so I'm going to
remove product key from here and instead
go to the product table and grab
category and drop it in there now watch
what happens now and this is probably
not what you expected this certainly
seems wrong and you see what's happening
and this is what I call the repeating
number pattern and you're going to see
it a few times and almost always it
indicates trouble not always there's
some cases where that's exactly what you
want but almost always when you see a
repeating number pattern like this
there's some trouble involved and the
first thing that I look for the very
first thing is that is there a
relationship between these two tables so
the category is coming from the product
table and the sales amount is operating
on the sales table or they connect it in
a relationship and you can see that
they're not connected in a relationship
there's no line indicating relationship
between them now well let's talk about
that what is a relationship I think
about it this way if you were in the old
world and you had the sales table let's
go over here and you have the sales
table which had the product key three
one four and you have a different table
product which had the same key of course
somewhere in there and the name
subcategory category how would you
handle that well you would do it using
vlookup gosh yeah written my share fair
share of vlookups back in the day so you
would say vlookup product key from this
table to that table and then gets
category subcategory essentially you
flatten the table out right and you get
all of that so you would have to write
quite a few we look up columns to get
all the product attributes
but in this new world you don't need to
do that you don't need to write an army
of vlookups you can simply connect these
two by a relationship so the same
columns that you would have used for the
vlookup you would have said we look up
this product key and find it there the
same column you're just going to drag
and drop and connect it together in a
relationship so that's all you need to
do and once you've done that now if I go
back to my report for you boom you see
that it's fixed it's working by category
and in fact this would work by any other
attribute I drop in there so if I drop
in color boom it shows me by category
color product category subcategory so
anyway all of those would work you can
slice and dice that measure by using any
attributes simply by the word shoe of
this relationship so relationships are
in a way the end of we look up so
imagine how many wheel cups you would
have had to write to flatten not just
the product attributes but territories
customer calendar and now we're just
going to run through this and connect
all the other tables and this is so much
easier than writing that army of vlookup
second we're going to connect customer
ticket give your customer key and last
one sales territory key to sales
territory perfect actually
I'll show you this one again so this
might change frankly over the release
different releases of our bi desktop
I've seen different behavior but at
least for right now it doesn't matter
which direction you drag and drop it so
even if I drop drag and drop from the
lookup to the data table it still
connects it and it still connects the
same way and lastly if you want complete
control over that you can go to the Home
tab and click manage relationship and
you can either edit or create a new
relationship from there so I can say I
want to connect sales table to
territories and usually it selects the
matching column if not you can
selectively yourself is yep use these
columns many who want is good single is
good and hit OK
all right so now we have all the tables
connected we can go crazy we can just go
in there and now we can slice and dice
the sales amount using really any table
we can say customer or pick something
from territories or a combination of the
elements maybe I can add customer gender
in here as well so you get the idea once
the relationships out there they let you
slice and dice the data table any way
you want so we have our sales table
connected but we haven't shown any love
to our budget table let's take a look at
that so we're going to bring in our
budget amount perfect I'm going to
change it to a table great make that a
little bigger and again if we try to add
an attribute from the product table if
we add category we get the same
repeating number patterns but we know
exactly what to look for the first thing
we look for is is there a relationship
between you stated two tables that isn't
so we're just going to create one using
the key already there product key and
while we're here we're going to connect
the month to the calendar table as well
so we're here and you can see this fixed
itself and again you can kind of slice
and dice this any which way you want so
we're going to talk a whole lot more
about relationships about data look-up
tables the direction of the
relationships many do one one-to-many
all of that stuff is coming later but
next up we're going to talk about one of
my favorite topics measures 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 power on my friends
you