In this video we would talk about Power BI relationships and a very important
concept of data and look-up tables. Keep watching, so we're done with the
first step in building our data model which is to get data into the model. The
next thing we're going to do is to define the relationships and after that
we're going to take a look at defining our measures. So for this lesson let's
focus on relationships, so what I've done here is to start off with a clean slate
I've deleted any relationships that are detected automatically. It does try to do
that now if you if you choose to you can also turn that setting off by going in
options you can say data loading you can uncheck this option and say do not try
to auto detect new relationships. But I've deleted those relationships I
want to start from scratch and the first thing I want to do, is talk
about the concept of data table and look-up tables. The tables at the bottom
here I've intentionally arranged in here these are the ones that are referred to
as data table and the ones on the top are what I call the lookup table. Now
data table has a few telltale signs for one, it's got a lot of numbers and it's
got interesting numbers in there like sales and number of units ordered and
all that stuff so interesting numbers which we want to do math on. We want to
sum it up, we want to find the average, we'll assign and find the
max min all of that stuff, right so we want to do some math on it. So it's got
lots of numbers and it's very tall it's got a lot of rows the sales depending on
the size of the business could have a thousand transactions could have a
million or millions of transactions that's how tall it can get. The lookup
table on the other hand are pretty compact and they're very textual so if
you look at our product or let's say our customer table you can see I would
scroll down a little bit yeah there are a lot of rich text attributes so this is
more human friendly, humans frankly don't do that well with numbers unless they have
summarize but text, yeah we can absorb that, again that makes more sense to us than
numbers. So it's got these rich text attributes product names subcategory
bikes and so on and so forth, right. So they are text heavy but they're not as
tall, so if you have a hundred million transactions in your sales table you're
not going to have a hundred million products in the product table or a hundred
million customers in your customer table, right. So they are, they're compact, but because of
their all of their text attributes they can be wide, right. So we'll talk more
about this as well later but for now I wanted to kind of set that up for you
and another way to look at the the lookup tables is that they are the who,
what, where, when, how, so sales is the transaction that's an
event that occurred well, who bought the product, what did they buy, where did they
buy it, when did they buy it, and how can be the casual bucket, like was there
sale involved what kind of credit card to use all that sort of stuff, right. So
the look-up tables are the who, what, where, when, how of the transaction which
is represented in our data table. Now let's go over to our report view and we're
going to add our first element. So here,this one is really easy and if you work
with pivot tables in Excel then you can handle this you see the field list which
looks just like the pivot table field list and often I recommend that you
start with the simplest element which is table that's frankly what I started with.
So I'm just going to click on an element to place it there so I place the table
here and now I have, I can drag values to it. So what I'm going to do now is just
add sales amount to this. Now you can drag it here or you can drag it on the
element itself. Now what it's doing here is actually it's listing out all the
sales amounts and now this this may be different sometimes it varies sometimes
it tries to automatically sum but in any case you can control that behavior. So if
we kind of zoom it over there you can see that you can say now don't don't
summarize or you can say no sum it all up and that's what we're going to do and
we're going to get this wide big number. Now let me fix the format a little bit
I'm just going to go to format modeling and the data type is decimal that's
great I want to keep the decimal places I just don't want to show the decimal
places I'm going to change the format not show
the decimal places let's make that a little bit bigger that's easy to see so
that's our sales amount, perfect and now if I added a product key in there let's
see what happens if I add product key, boom! you can see the breakdown of sales
amount by the product key. So for each product key you can see how much sales
total sales did we incur. 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.