# DAX5_001

0 (0 Likes / 0 Dislikes)

One of the really powerful things about the Power BI engine
is the ability to put in multiple tables potentially from different data sources
and create relationships between those tables to help model your data.
DAX also includes a whole range of functions for using and managing those relationships.
For example, being able to go and take the related rows of data from a different table
and then use those in your calculations somewhere else.
Let's hear Alberto talk more about how you can use those DAX functions.
[Alberto] There is a set of functions which are useful to follow relationships.
And that answers a question that somebody asked a few seconds ago—
how, if I want to compute, for example, the sum or compute values
with columns which are not in the current table but are in another table?
Provided that there is a relationship between two tables,
you can use both related and related tables.
And it's easier, again, to move to the demo machine.
Wow! It works now. Thanks.
And—
and try using related and related table.
For example, on the Sales let's go to the model. On the model
you see that Sales—no, not you—
Sales is here. Sales has a relationship with Product,
with Store, with Customer, and so on.
But basically it has a relationship with Product. And I mentioned
that I want to compute this time the sales amount computing the sum,
an iteration of our sales, get the Quantity and multiply by the unit cost
or by the unit price, which is not in the current table. But it's in a related one. Okay?
I can easily try to modify my measure.
I guess it was called Total Sales.
And you see that Total Sales iterates over Sales and computes Sales[Quantity]
multiplied by Sales[Unit Price].
What happens if I write Product[Unit Price]?
If I try to write Product[Unit Price],
even though there is a relationship between Sales and Product,
I simply cannot access a column which is in a different table.
So in that table that is not the current one over which I'm iterating.
If I want to do that, I can do that but I need to surround the function with Related.
Related lets you follow a relationship in many to one.
So go from Sales to Product, grab the value of the product.
Then the single line containing the product referred by the current cell
and then use the value of the unit price.
If I do that this way, I create my measure and that works just right.
So if I want to again get rid of the Unit Price column in my fact table
and just use a relationship to grab the value from the other table.
Related works through any chain of relationship from the many side to the one side.
Are you familiar with many side and one side of relationships?
Say yes or no so I hear. >> Yes.
Cool. So related works from the many side to the one side
because you need to grab a single value. So from the many side sales,
there is only one product for given sales and related gives you access to value of the individual product.
And it works just fine.
So it works on any chain relationship provided that they are always
many to one, many to one, many to one.
Sometimes we want to do the opposite.
Imagine that you want from the one side to get access to the many side of the relationship.
Now Related would be in trouble because Related expects to return a value.
And if I am on the one side—imagine that I'm on a product—
and I want to get access to the many side, I will not get a single row.
I will get many of them because there are many sales for any given product.
So what I need to do in that case is not use Related but instead use Related Tables.
If I go, for example, from Product,
we can go to the end and create—imagine that we want to create
a calculated column to tell me how many sales I did just counting the number of sales
for each given product. I can easily do that by creating a new column.
Let's call it NumberOfSales.
I hate what things that does. NumberOfSales.
How do I compute the number of sales? Any idea?
Sorry?
Distant count—count—count what?
Count—that is nice. It just does count.
Getting to use Count Rows. Count counts the number of values in a column.
Count Rows counts the number of rows in a table.
And then I use Related Table.
Sales. This is going row by row to go on the sales table
and simply count the number of rows and then store the result
in my column. And now I have for any given product the number of sales
over all time, over all whatever.
Okay?
Oh!
That's the difference. You still have not understood I'm Italian.
"Could you zoom in?" is hard to hear. Zoom is much better.
That I can understand. It's more Italian.
So NumberOfSales is just simply count rows NumberOfSales.
Now what if I want to compute for every product the amount sold?
The pattern is very similar. By RELATEDTABLE(Sales) I can get access to the sales
of that current product. But then I don't want to simply count the number of rows.
I need to create a new column. This time I'll zoom earlier.
Let's call it SalesOfProduct.
And then I use SUMX (RELATEDTABLE (Sales).
And then I get Sales[Quantity] multiplied by Sales [Unit Price].
Now why are you doing columns instead of measures?
That's an interesting question. Why am I doing a column?
Because I want to compute for this given product
the amount sold for that product.
This might be useful. Imagine that I want to perform something like ABC analysis
or product analysis and I want to be able to identify the product that sold most
and then divide them in categories—so the most sold product,
the intermittent ones, and the product that never sold anything—
then I did a column because I want to consolidate the values once and forever.
Or if I want to classify products based on the number of times I sold them,
having that as a calculated column is useful as an intermediate step
in computing something that complex.
So these are just the sales of that given product,
and the product needs to be given, whereas if I want to show the sales in the report
then I don't need to calculate this column. I can simply put the product name
on my report of the sales amount, and I get the value there.
It's useful to have it as a calculated column only if it's part of a more complex calculation
that needs to happen later. Make sense?
When you're—
I guess the last parenthesis was wrong.
Real quick on that, you're doing a filter?
So you treat the calculated table—okay is the calculation
of a column different than a measure? Like if you're saying just show me,
then you're actually doing visualization, you start filtering on it.
Will that change depending on whether you use a measure or a column?
Again, another interesting question.
Is it going to change the value if you put it in a report and then you further filter the report?
And the answer is no. The reason is very simple.
Calculated columns are computed at refresh time.
So they are computed once and forever. Once they are computed,
this is their value. If I put the sum of this calculated column
into product table or into a report and then I slice the report by year,
I will always see the total sales of the product for every year the same value.
I will not see any kind of filtering. So I have that Sales of Product,
and if I put it aggregating by sum—
oh, it's in the product table—
Sales of Product—you see that the number is always the same.
It does not depend in any way from the data.
Make sense? Because the number has been computed on the product table
and no matter how you filter the data, data is not able to filter the product.
And in any case they would never filter the product in the correct way
because I pre-computed it as a calculated column.
If I do the same code as a measure, then everything is different.
It's computed at query time, and in that case the calculation will depend
on the filtering that you put on the report.
Okay?
Now let's go back to the slides.
So, you can use Related to move values from one place to another.
In this case you can use Related to move Category and Subcategory
from the product table to the sales and from the categories to the sales.
Or you can use Related to do further calculations like SUMX
and compute the sum of sales.