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)|
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.
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.
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.