Lookups in PowerPivot Calculated Columns - Using the RELATED Function

In my previous post we looked at how Calculated Columns work in PowerPivot. One limitation you may have noticed though was all of the calculations were done using values in that individual table. What if you wanted to lookup a value in second table, based on a value in the first table, and return a value from that second table. Yes Virginia, not only is there a Santa Clause but there’s also an answer for us in PowerPivot’s RELATED function.

In addition to the standard Excel functions, PowerPivot provides a set of its own functions for working with its data. These new functions are collectively known as Data Analysis eXpressions, or DAX for short. By now you’ve probably guessed that the first function from the DAX toolbox you’ll want to learn is the RELATED function.

Read more...