Finding a value based in another table based on name and if a value is within a range

[Kota]

Hi,

Stuck here with a difficult one and hoping the community could help.

Table A is a list of completed work with items, qty, size of job and the user who completed the work

Table B is a reference table with the Item, size range, user1 price, user2 price, user3 price

I am trying to calculate the price on table A of the individual job by matching the following in Table B correct item, size of job fits in range, with user price. then Multiplying it by the QTY in table A.

There are duplicate items that have different prices per user based on the size range in TABLE B.

Not sure where to start on this, Hopefully, the description makes sense.

thanks 

[V-lianl-msft]

Hi @Kota ,

Could you kindly provide some sample data and the results you expect? So we can do more tests to see if we can help you solve the problem.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Kota , How to use user here , You can get a new column like

new column in Table A=

MAxx(filter(Table2, Table2[item] =Table1[item] && Table2[size] >=Table1[size Min] && Table2[size] <=Table1[size Max]), Table2[User Price 1])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8