The RELATED function returns the value related to the row being considered from a column of another table (remote table), assuming that there is a relationship between the table in which you are working (current table) and the remote one, that this relationship is "many-to-one" or "one-to-one" type, and that the remote table is the one corresponding to the "one" side -that is, it is a dimension table-.
In other words, this function allows, from a table, to extract the field associated with each record in a related dimension table.
RELATED(
column
)
- column: Column of the remote table to be retrieved.
The RELATED function returns the remote table value related to the current table row being considered.
This function requires that a relationship exists between the current table and the remote table, and this relationship must be of type "many" (in the current table) to "one" (in the remote table) to be able to return a single value (also the "one-to-one" relationship is valid). When looking for the correct value to return, the function examines the entire remote table regardless of the filters that have been applied.
The RELATED function requires a row context. This means that it can only be used in calculated column expressions -where the row context does not imply any ambiguity- or as a nested function within an expression that uses a table scan function, such as SUMX.
If we have a sales table (Sales) that includes a reference to the product sold, and another table of products (Product) containing the details of each product, including its name in the ProductName field, we can add the calculated field 'Product'[ProductName] to the Sales table with the following expression:
Product name = RELATED('Product'[ProductName])
Now suppose that in the Country field of the Geography table we have the country in which the reseller who made each sale is located. Let's look at sales by country first. For this we simply take the measure that adds the sales (Sales) and the Country field to a table type display:
The Sales measure is summing the field that contains the sales figure, SalesAmount:
Sales = SUM(Sales[SalesAmount])
Now suppose that we want to be able to analyze sales in all countries other than the United States. A first option would be to use the CALCULATE function to revalue the Sales measure:
Non USA sales = CALCULATE([Sales], Geography[Country] <> "United States")
We can take this new measure to a card type display to see the result:
The figure is correct, but if we take this measure to a table together with the Country field to show sales outside the United States segmented by country, the result is the following:
Of course, this is not what we wanted, but the CALCULATE function is modifying the context to always consider all countries other than the United States.
What we want is to be able to use the SalesAmount field that contains the sales figure but including only those sales records that do not correspond to resellers located in the United States. To begin with, we can build this table that only considers the countries of interest using the FILTER function. The pseudocode would be something like:
Filtered table = FILTER(
Sales,
Countries other than the United States
)
If the Sales table included a "Country" field, it would be enough to add Country <> "United States" instead of "Countries other than the United States", but it does not include it. This information relative to the country in which the reseller is located is in another table, the aforementioned Geography table, a table that is not directly accessible as an argument of the FILTER function. It is in this situation that the RELATED function comes in handy. Assuming that there is a relationship (of many-to-one or one-to-one type) between the sales table and the geography table, we can access from the first to the Country column of the second with the expression:
RELATED(Geography[Country])
Now the code for the filtered table would be as follows:
Filtered table = FILTER(
Sales,
RELATED(Geography[Country]) <> "United States"
)
As mentioned, the RELATED function requires a row context, which we can achieve using a table scan function like SUMX, which is exactly what we are looking for to calculate total sales:
Sales non USA =
SUMX(
FILTER(
Sales,
RELATED(Geography[Country]) <> "United States"
),
Sales[SalesAmount]
)
What we are doing is adding the SalesAmount field for each of the rows of the filtered table, and this is composed of the set of sales records that do not have the United States associated as a country, but without excluding other possible filters that come given by the context.
In this way, if we take this measure to a card:
...we see that the total is correct. And if we take it to a table together with the Country field of Geography:
...we see that the measure is correctly adapted to the context, just as we wanted.
