When we looked at the SUM and SUMX expressions in an earlier article and video we introduced the use of RELATED.
You will remember this expression;
=[Qty] * RELATED(products[Sales Price])
Where we took the qty sold from the sales table and we went across to the related products table and found the relevant sales price and multiplied them against each other.
RELATED works as a lookup and for it to work, there must be a relationship between the two tables. Related goes from the Many side of a relationship to the One side of a relationship. In this example, in the sales table we will have many sales of a product. However in the products table each product is only listed once. So the related function moves from the many to the one side and will return a single related value.
We could add a new calculated column just to show the related expression on its own.
So RELATED is in a calculated column. This means the row context is automatically defined as the current row. And so DAX says to its self, go over to the related table ‘Products’ and look for a product that is the same as the one in this row. Then return the cost price for that product. Easy enough right?
RELATEDTABLE is a table function. Like RELATED, RELATEDTABLE requires a relationship between the two tables. RELATEDTABLE goes from the One side of a relationship to the many side. As it goes to the Many side, it is unable to return a single value, but instead it returns a table of values. The table of values that it returns can be based on filters.
The syntax for RELATEDTABLE is = RELATEDTABLE (Expression, Filter 1, Filter 2)
At this level we are not going to look at the filter element of the expression. Instead we are going to look at two simple business problems that can be solved with RELATEDTABLE in the sample dataset we have been using.
In the products table we want to find out, how many sales were made for each product? We are not looking at how many units were sold, just how many sales were made that includes that product.
We can set up a new calculated column and enter the expression
COUNTROWS will count the rows in a defined table. In this case we want to go over to the sales table and count the number of times the product is found. The products is on the one side of the relationship as each product is only listed once in the products table. On the sales table the product will appear multiple times and so a table will be returned.
Because this is a calculated column the row context is automatically defined to be the current row. So DAX says to its self, take the product from the current row, go to the related table sales and return a table of sales that contains only the product as defined by the row context. Then take this table and count the rows.
COUNTROWS is an iterator as it will iterate over each row of the returned table.
Putting it all together
Now let’s say you wanted to find out what value of sales you have made for each product in the products table.
To solve this lets first go back to our SUMX expression that we created in an earlier lesson in the sales table.
SUMX(Sales, Sales[Qty] * RELATED(products[Sales Price]))
We wanted to calculate the total sales based on the Units sold in the sales table and the product selling price in the products table. We calculated this as a measure and we defined the row context by using SUMX to iterate over each row of the table. For each row of the table, DAX took the units sold for that row, then went over to the related table and found the product and the related product selling price and multiplied one by the other, then it done the same on the next row and the next row and then finally summed the values together.
Taking what we learned with SUMX we can now apply this to our current problem.
In the products table we could we could set up a measure to calculate the following
SUMX is an iterator, that will iterator over the related table Sales and find the sales for the product in the current row of the products table, then when it finds all the sales that contain this product, it will return a table to include these values. It will then sum these sales together and return the value.
But what if the sales value is not available in the sales table? What if we need to calculate it based on the units sold in the sales table and the selling price in the products table?
We can create a calculated column and use the following Expression
=SUMX(RELATEDTABLE(Sales),Sales[Qty]* products[Sales Price])
This expression will go to the related table sales and find the related quantity for the current product and then multiply this by the sales price in the products table, it will then go to the next row in the products table and do the same.