Microsoft Office Tutorials and References
In Depth Information
Note The row context is limited to a single row, and relationships between tables do not
propagate the row context to other tables by themselves. The RELATED function propagates the effect of
row context to a related table, provided that a valid relationship to a related lookup table exists.
After you define the relationship between Orders and Channels, the DiscountedAmount
column can be calculated for each row, as you can see in Figure 6-18.
FIguRE 6-18 Valid calculation for the DiscountedAmount calculated column.
On the opposite side of the relationship, you might want to calculate over the set of rows
related to a Channel selection in the Channels table. For example, you might want to
calculate the total number of orders for each channel in a calculated column of the Channels
table, as in the OrdersCount calculated column shown in Figure 6-19.
FIguRE 6-19 A calculated column in a Channels table using the RELATEDTABLE function.
The RELATEDTABLE function returns a table composed of only the rows that are related
to the current row context. That table can be used as a parameter to any aggX function
or to other DAX functions requiring a table, such as FILTER or COUNTROWS (which we
used in our example).
Important You should avoid using the RELATEDTABLE function when there are alternatives
based on CALCULATE that get better performance. Although this might be not convenient
for calculated columns, it is almost always possible for measures and when it is necessary to
manipulate the filter context, as you will see in the following sections.