Refer to a Related Table in a Formula

March 20, 2023 - by Bill Jelen

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 Power Pivot 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 Power Pivot 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.

Gotcha: When you define a calculated column in the Power Pivot 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash