Power BI/PowerPivot: Using LOOKUPVALUE() to Emulate Relationships

Today, we're going to talk about a neat function in DAX called LOOKUPVALUE().  This function is pretty aptly named, as it simply returns a value from a different (or possibly the same) table.  Let's start with a very simple data model.

Sales Data

Data Model

We can see that we have two distinct transaction tables, Sales and Marketing, which roll up to a single Customers lookup table.  Pretty much all of these could be accomplished using a different function.  However, we always strive to find the easiest solution to understand and implement. That's where LOOKUPVALUE() shines.  Let's look at some options for looking up values across (or within) tables.

Retrieving a Value from the Same Table

Sometimes, you need to retrieve a value from a specific row in the table you're already in.  This is one of the great use cases for LOOKUPVALUE().  For our scenario, we want to compare the Sales for each row in the Sales table to the Sales for January for that same customer.  Let's see how it's done.

January Sales (Formula)

January Sales (Results)

All we have to do is define the value we're looking for, then a unique set of identifiers to identify where we want to look in the target table.  In this case, we're looking for the same [Customer ID], and a fixed [Month Start Date] of January 1, 2016.

Retrieving a Value From Up the Hierarchy

Now, what if we wanted to retrieve a specific value from the lookup table.  In our case, let's say that we want to pull Customer Type down into the Sales table.  We could use LOOKUPVALUE() to accomplish this as well.  However, there's a far simpler way, the RELATED() function.  The RELATED() function uses Row Context to retrieve a value from up the hierarchy.

Customer Type

The RELATED() function is far simpler than the LOOKUPVALUE() function.  All you have to supply is the field you are looking for.  However, RELATED() only works when you want to retrieve a single value from up the hierarchy and requires an active relationship.

Retrieving a Value From Down the Hierarchy

Now, what if we wanted to pull a value from the transaction table into the lookup table.  Ordinarily, this is done using aggregations and the CALCULATE() function.  However, in some cases, you want to retrieve a specific value with no aggregation.  Let's try pulling the Sales for February up into the Customers table.

February Sales

This time, we need to provide Customers[Customer ID] because we are querying from the Customers table.  The rest remains pretty simple.

Retrieving a Value from an Unrelated Table

We tried our best to find an example of this that works in a useful.  Simply put, this is VERY sticky territory.  DAX is built around utilizing relationships.  Therefore, it can become quite difficult to work without relationships.  To summarize, if you are trying to connect two tables without some type of relationship chain, you may want to reconsider your modelling.  If anyone in the comments can find a good example, let us know!

Hopefully, this post opened up your mind to the possibilities of using LOOKUPVALUE().  It's not a unique function by any means, but it sure is easy and intuitive to use.  That's worth quite a bit in our book.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
BI Engineer
Valorem Consulting
@BreakingBI
www.linkedin.com/in/bradllewellyn
llewellyn.wb@gmail.com