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
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.
To show the related expression on its own, we will add a new calculated column. In this column we will use the expression:
=RELATED(Products[Cost Price])
With RELATED in a calculated column, the row context is automatically defined as the current row. Therefore 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. When you find the corresponding product, return the cost price for that product. Easy enough right?
RELATEDTABLE
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(RELATEDTABLE(Sales))
COUNTROWS
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 (RELATEDTABLE(Sales),Sales[Sales])
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.
Learn and Earn Activity
To earn tokens on this learning resource , using the blockchain based comments below answer the following questions:
Briefly describe the difference between the two functions RELATED and RELATEDTABLE
Describe to us a data set that can be found in your organization where you would need to use RELATED or RELATEDTABLE
Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox
SIGN UP NOW
In return for this Tip – DAX for Power Pivot and Power BI – Related and RelatedTable – I ask that you share this post with your friends and colleagues
The Excel Club blog is powered with blockchain. We are a tokenized website where you can earn while you learn. To earn tokens you must partake in the practice activity and post your answers, questions or feedback in the comments section below. Click here to find out more
Cross posted from my blog with SteemPress : https://theexcelclub.com/dax-power-pivot-power-bi-ssas-related-and-relatedtable/