Excel: Refer to a Related Table in a Formula

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I am entering a formula in the Fact table. I need to lookup a value from Product 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 Fact table, you can enter the following:

=[Quantity]*Related(Products[ListPrice])

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.

Here is the easy way to build the formula: Type the equals sign, click Quantity, and then type the asterisk. Type the first few letters of the Product table. You can then choose a field from the list. Highlight the field. Press Tab to insert the field. Type the closing parentheses. Press Enter. Right-click the header to rename.

  1. Formula AutoComplete helps so you don't have to remember the syntax.

    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.