DAX - How do I set up the equivalent of a VLOOKUP?

Hi,

I have a couple tables uploaded to a data model in Power Pivot, and I'm trying to just do a simple lookup between tables. I suppose if I can't solve this by using measures in DAX that I'll just add another column to the one table and do the VLOOKUP there, but I thought it would be good to know how to handle this in the data model in Power Pivot. If I have Table 1 with a column that I'm using for the lookup values, and Table 2 has the lookup range (one column) and the return values (another column), how do I create a measure that would accomplish the same thing as a VLOOKUP for my pivot table?

Thanks!

Hi,

Please see the attached file. I have already created the necessary relationship. To sum up, I have the business unit ("BU") linked between my two tables (one table each in the "RETURNS DATA" tab and the "CPP Prices" tab), and I want the CPP for each company to be displayed in the pivot table in the "Dynamic TOTAL SUMMARY" tab. Each company should have a 1:1 relationship with a division, and each division should have a 1:1 relationship with a CPP. However, when I try to add CPP to the pivot table, it forces you to pick a sum, average, count, etc., and I just want the single value from the table to appear.

To clarify, I'm looking to include the CPP value from the CPP_Price_Calendar (not the CPP from the Returns_Table) table in the pivot table.