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