This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Refer to a Related Table in a Formula
Problem: I am entering a formula in the Sales table. I need to lookup a value from Rates table. I’ve already defined a relationship between the tables.
Strategy: The promise of PowerPivot is that you won’t have to do VLOOKUPs anymore. When you are building a calculated field, you will have to use a simpler lookup function called RELATED. In the Sales table, you can enter the following:
That is a simple one-argument lookup function. This function tells PowerPivot to follow the defined relationship and retrieve the value from the other table.
When building this formula, you can type the equals sign, click Revenue, and then type the asterisk. You then have to remember the name of the field in the other table. I start by typing the first letter of that other table. The Formula AutoComplete drop-down will then show you the list of available fields.
Figure 956 When referring to a field in another worksheet, use Formula AutoComplete.
Gotcha: When you define a calculated column in the PowerPivot window, that value is calculated for every row in the table. This can be a lot of overhead for a 100 million row dataset. In contrast, the DAX measures are calculated only once for each cell in the pivot table.
Figure 957 The Bonus column uses a lookup value from Rates.