I deliver a lot of training on Power BI and I also deliver a lot of training on how to use the Data Model and Power Pivot in Excel. One question that comes up all the time in the trainings, especially when we get on to DAX, which is a formula language that is used in both Power BI and Power Pivot, is “How do you do a VLOOKUP in DAX?”
The answer that I give is that DAX doesn’t have a VLOOKUP or XLOOKUP function but it does have a very similar function called LOOKUPVALUE.
In this video I explain how to use it. Although I’ll be using Excel in the video, the good news is that it works the same in Power BI.
“But why would you use LOOKUPVALUE when you have the ability to create relationships between tables?” is often a follow-up question. Consider this situation… there are 2 tables in the Data Model. The ORDERS table contains details of sales transactions and the CURRENCY RATES table contains currency exchange rates
It’s not possible to create a relationship between the tables because there isn’t a common column with unique values in one of the tables that can act as a key to link them.
So if I need to calculate the Revenue in USD for each transaction I would need to add a new column to the Orders table and use the following formula to generate the values:
=Orders[Total Revenue] * LOOKUPVALUE('Currency Rates'[Rate], Currency Rates'[Currency To], "USD")
This generates a value for each row in the new column by multiplying the Total Revenue by the value in the Rate column in the Currency Rates table where Currency To is USD.
Download a copy of the file used in this video: https://share.zight.com/04unAonW