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.

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.

clip_image001

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.

clip_image002

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.

About these ads

3 Responses to “Lookups in PowerPivot Calculated Columns – Using the RELATED Function”

  1. the xbox 360 made by microsoft Says:

    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.

  2. Brad Says:

    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!

    • Yuriy Says:

      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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: