RELATED() pulls values from different tables based on relationships established in the relationships tab. This is useful and makes it so we don’t necessarily need to append or merge tables to get the same result.
For example, we can create a new calculated column in our fact table Sales that pulls in related information from two other tables without needing to append or merge tables:
Requirements:
- Calculated column is created in the table that connects all other tables being called in the calculation (so this can’t be created in CampaignDim above, but works in Sales)
Steps:
- Create a new calculated column in the fact table (or table connected to all columns about to be referenced)
- String/text example:
- Channel/Cat = RELATED(CampaignDim[TrafficChannel]) & “, ” & RELATED(ProductDim[Category])
- Mathematical example:
- TotalSales = Sales[Units]*RELATED(ProductDim[UnitCost])
Tips
- “&” acts to concatenate multiple strings more efficiently than using CONCATENATE()
- Any text being concatenated to other strings or related values should be inside quotation marks (“”) as the comma is in the example above
- RELATED() requires established relationships, where LOOKUPVALUE() does not
- Learn more on the RELATED() function on MSDN