LOOKUPVALUE -- Recreate VLOOKUP getting 1st and 2nd matching value in DAX

[nikkollai]

Hello Everyone,

I tried this for a few hours and officially give up. It would be great if someone could look at this with fresh mind and figure it out.


So, I have two table that are linked with bridge table and I am trying to pull cost prices from cost table into price table through LOOKUPVALUE DAX function.

LOOKUPVALUE(CostTable[Cost], CostTable[Product_ID], ProductTable[ProductID])

The problem is that I have same values for cost and DAX is throwing an error. Cost is different for every month (3rd column), so my goal is to re-create a vlookup first (cost) and second (month) matching values. The challenge is to filter CostTable[Month] inside a lOOKUPVALUE function. IT's ok to hardcode the number of the month inside the LOOKUPVALUE function.

If you have any ideas I will be highly thankful.


COST TABLE

Product_ID	Cost	Month	Year
1516554	195.80	5	2016
1516583	194.80	6	2016
1516594	194.80	4	2016
1516560	194.07	3	2016
1516561	193.99	1	2016
1516571	193.87	4	2016
1516519	193.87	6	2016
1516530	193.86	5	2016
1516589	193.85	3	2016
1516533	193.84	2	2016
1516521	193.79	3	2016
1516569	193.21	5	2016
1516591	192.50	5	2016
1516586	192.50	4	2016
1516534	192.50	3	2016
1516518	192.50	2	2016
1516547	192.50	6	2016

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

PRODUCT TABLE

Product_ID	Cost
1516554	value for 6 month
1516583	value for 6 month
1516594	value for 6 month
1516560	value for 6 month
1516561	value for 6 month
1516571	value for 6 month
1516519	value for 6 month
1516530	value for 6 month
1516589	value for 6 month
1516533	value for 6 month
1516521	value for 6 month
1516569	value for 6 month

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Matt Allington]

Power pivot is different to Excel, and I am guessing you are going about this the wrong way. Can you post a sample workbook with all 3 tables loaded? Providing info on just 2 of the 3 tables makes it difficult to help you.