RELATED

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:

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:

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:

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:

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:

Now the code for the filtered table would be as follows:

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:

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.