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
<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
PRODUCT TABLE
<colgroup><col><col></colgroup><tbody>
</tbody>
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>