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.

Let’s start with the same Excel 2010 workbook we had at the end of the lesson Combining Data from Multiple Sources in PowerPivot. If you recall we had imported data from the AdventureWorksLT2008 database. To that we added data the *CountryInfo* table, which we’d typed in to an Excel spreadsheet.

At the time we used this to get the *CountryAbbr* column, and you may have wondered why we also included a *DiscountRate* column. It’s this lesson where this *DiscountRate* will come in to play.

If you recall from that post, we used PowerPivot’s *Manage Relationships* feature to create a link that ultimately connected the *SalesOrderDetail* table to the *CountryInfo* table. This groundwork enables us to lookup values very easily. Here is a simple example. Go to the *Add Column* column of *SalesOrderDetail* and enter this formula into the fx area:

=RELATED(‘CountryInfo’[CountryAbbr])

When PowerPivot gets done, the abbreviation for each country to be shipped to appears in this column. This can be used to “flatten out” some of your data. However it’s much more useful when used as part of a calculation. Delete the column we just added (right click on the column header and pick *Delete Column* from the menu).

Looking in the *CountryInfo* table we see the *DiscountRate*. A value of 0.04 means our US customers get a discount of 4% off their *LineTotal*. So in our *SalesOrderDetail* table we want to take the *LineTotal* and calculate a new value based on the rate, which is stored in the *CountryInfo* table. Simple enough using the PowerPivot **RELATED** function.

=[LineTotal] * (1 – RELATED(‘CountryInfo’[DiscountRate]))

This is fairly simple math, we take the DiscountRate from CountryInfo (for US, 0.04) and subtract from 1 giving us 0.96, or 96%. This is then multiplied by the LineTotal giving us our new discounted LineTotal amount, which I renamed to *DiscountedLineTotal*.

Hopefully these simple examples will give you a glimpse of the immense power RELATED offers. It will allow you to combine data from multiple tables to create in-depth analysis that previously required a specialist in Business Intelligence to create.