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.
Arcane Code 
Just wish to say your article is as surprising. The clearness on your post is simply great and i could think you’re a professional in this subject. Well together with your permission let me to grasp your feed to keep up to date with forthcoming post. Thank you 1,000,000 and please keep up the gratifying work.
As someone who’s accustomed to writing in SQL and SAS, I was surprised to find there’s a need to use such a function in order to reference a field in another table. For me, it begs the question, what was the point of establishing the relationship across the tables in the earlier steps in the first place? I struggled before finding this post of yours as I kept trying to reference the fields in other tables by preceding the reference with the table name and getting a context error. Was starting to wonder if there was any point in using PowerPivot at all. Hopefully the built in interface in Excel 2013 will be smoother. Thanks for the info though!
You can use RELATED function ONLY after establishing relationships. Once relationship created, you type “=RELATED(“, click on a column of related table you want to return, close the braket “)”, and press ENTER. Here is what you should get: “=RELATED(CostCenters[CCName])”, where CostCenters is name of related table, CCName – name of the column in CostCenters table which I want to return.