Relationships and Lookups in Formulas

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

One of the most powerful features in PowerPivot for Excel is the ability to create relationships between tables and then use the related tables to look up or filter related data. You retrieve related values from tables by using the formula language provided with PowerPivot for Excel, Data Analysis Expressions (DAX). DAX uses a relational model and therefore can easily and accurately retrieve related or corresponding values in another table or column.

You can create formulas that do lookups as part of a calculated column, or as part of a measure for use in a PivotTable or PivotChart. For more information, see the following topics:

Measures in PowerPivot

Calculated Columns

This section describes the DAX functions that are provided for lookup, together with some examples of how to use the functions.

Note

Depending on the type of lookup operation or lookup formula you want to use, you might need to create a relationship between the tables first. For information about creating relationships, see Relationships Between Tables.

Understanding Lookup Functions

The ability to look up matching or related data from another table is particularly useful in situations where the current table has only an identifier of some kind, but the data that you need (such as product price, name, or other detailed values) is stored in a related table. It is also useful when there are multiple rows in another table that are related to the current row or current value. For example, you can easily retrieve all the sales that are tied to a particular region, store, or salesperson.

In contrast to Excel lookup functions such as VLOOKUP, which are based on arrays, or LOOKUP, which gets the first of multiple matching values, DAX follows existing relationships among tables joined by keys to get the single related value that matches exactly. DAX can also retrieve a table of records that are related to the current record.

Note

If you are familiar with relational databases, you can think of lookups in PowerPivot as similar to a nested subselect statement in Transact-SQL.

For more information about the relational model used in PowerPivot, see Relationships Overview.

The RELATED function returns a single value from another table that is related to the current value in the current table. You specify the column that contains the data that you want, and the function follows existing relationships between tables to fetch the value from the specified column in the related table. In some cases, the function must follow a chain of relationships to retrieve the data.

For example, suppose you have a list in of today's shipments in Excel. However, the list contains only an employee ID number, an order ID number, and a shipper ID number, making the report hard to read. To get the extra information you want, you can convert that list into a PowerPivot linked table, and then create relationships to the Employee and Reseller tables, matching EmployeeID to the EmployeeKey field, and ResellerID to the ResellerKey field.

To display the lookup information in your linked table, you add two new calculated columns, with the following formulas:

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

Before lookup

After lookup

Order ID

EmployeeID

ResellerID

100314

230

445

100315

15

445

100316

76

108

EmployeeID

Employee

Reseller

230

Kuppa Vamsi

Modular Cycle Systems

15

Pilar Ackeman

Modular Cycle Systems

76

Kim Ralls

Associated Bikes

Order ID

EmployeeID

ResellerID

Employee

Reseller

100314

230

445

Kuppa Vamsi

Modular Cycle Systems

100315

15

445

Pilar Ackeman

Modular Cycle Systems

100316

76

108

Kim Ralls

Associated Bikes

The function uses the relationships between the linked table and the Employees and Resellers table to get the correct name for each row in the report. You can also use related values for calculations. For more information and examples, see RELATED Function.

The RELATEDTABLE function follows an existing relationship, and returns a table that contains all matching rows from the specified table. For example, assume that you want to find out how many orders each reseller has placed this year. You could create a new calculated column in the Resellers table that includes the following formula, which looks up records for each reseller in the ResellerSales_USD table, and counts the number of individual orders placed by each reseller. These tables are part of the DAX sample workbook. For more information about sample data, see Get Sample Data for PowerPivot.

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

In this formula, the RELATEDTABLE function first gets the value of ResellerKey for each reseller in the current table. (You do not need to specify the ID column anywhere in the formula, because PowerPivot uses the existing relationship between the tables.) The RELATEDTABLE function then gets all the rows from the ResellerSales_USD table that are related to each reseller, and counts the rows. Note that if there is no relationship (direct or indirect) between the two tables, then you will get all rows from the ResellerSales_USD table.

For the reseller Modular Cycle Systems in our sample database, there are four orders in the sales table, so the function returns 4. For Associated Bikes, the reseller has no sales, so the function returns a blank.

Reseller

Records in sales table for this reseller

Modular Cycle Systems

Reseller ID

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

Associated Bikes

  

Note

Because the RELATEDTABLE function returns a table, not a single value, it must be used as an argument to a function that performs operations on tables. For more information, see RELATEDTABLE Function.

See Also

Concepts

Add Calculations to Your Reports, Charts, and PivotTables

Build Formulas for Calculations

Data Analysis Expressions (DAX) Overview

Aggregations in Formulas

Other Resources

Relationships Between Tables