Microsoft Office Tutorials and References
In Depth Information
Let us try to understand what is happening when you try to define this column. The Discount
column is in the Channels table, which is not the table in which you are defining the new
DiscountedAmount calculated column. For this reason, if you want to read the value from
the Channels table (which does not have a row context) you must use the RELATED function,
which propagates the row context to the related table, as you can see in the formula used in
FIguRE 6-16 An error caused by a missing relationship between the Orders and Channels tables.
However, the RELATED function you used in Figure 6-16 still generates an error, but this time,
the reason is that there is no relationship defined between the Orders and the Channels tables.
When you define the relationship as shown in Figure 6-17, the RELATED function evaluates
the column passed as the parameter by applying the appropriate row context. For example,
an expression in a calculated column of the Orders table is always evaluated in a specific row
context. Starting from that row, the relationship you defined is used to choose a row in the
Channels lookup table that has the same Channel value as the row evaluated in the Orders
table. In other words, you could say that the RELATED function propagates the row context
to another table by following the existing relationship.
FIguRE 6-17 The relationship between the Orders and Channels tables.