Get a corresponding price from another table using relationship through item table

[Anonymous]

Hi!

I have this pretty complex problem (at least for me) that I can't seem to solve by myself. So basically I have this table called "Latest purchase price", which has information about every items latest purchased. I also have an item table and then I have a trade agreements table that contains information about every item's trade agreements for different purchase quantities, thus the relationship between that table and item table is many-to-one. I'll post a picture here about the data model if that helps.  

I want to compare latest purchase price to the price that we have in trade agreements thus I need to get the corresponding value from the trade agreements table for the latest purchase. I'll post a mockup data here also so that you can try it yourself. 

I have tried to do this using summarize columns and sumx. The logic is that once I have the virtual table with trade agreements for given item, I can use filter condition to filter it only contain values where purchased qty is equal or greater than fromqty in trade agreements table. Still it is ambiguous so using minx I want to just get one value which should be correct. But as you can see from picture bellow my dax query is not working...

Trade agreement price = 
 var latest_qty = MAXX('Latest Purch Prices', 'Latest Purch Prices'[OrderedQty])
 var trade_agreement_qty = 
 FILTER(SUMMARIZECOLUMNS(
     vDimItem[ItemID], 
    TradeAgreements[AMOUNT], 
    TradeAgreements[QUANTITYAMOUNTFROM]),
    TradeAgreements[QUANTITYAMOUNTFROM] <= latest_qty)
 
 return
MINX(trade_agreement_qty, TradeAgreements[QUANTITYAMOUNTFROM])

Latest Purch Price Table:

ID	ITEM	PRICE	ORDEREDQTY
1	1234	1	1
2	1235	2	10
3	1236	11	3

vDimItem:

ID	ITEMID
1	1234
2	1235
3	1236

TradeAgreements:

ITEMRELATION	FROMQTY	AMOUNT
1234	0	1
1235	0	5
1235	10	2
1236	0	10
1236	5	5
1236	10	2

Goal/Result:

ID	ITEM	PRICE	ORDEREDQTY	TRADEAGREEMENT PRICE	Difference
1	1234	1	1	1	0
2	1235	2	10	2	0
3	1236	11	3	2	-1

[Jayee]

Hi @Anonymous 

Use the "Related" DAX function and get the value from one table to another table.

If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!

[Anonymous · Accepted solution]

Sorry but that does not help since there is one to many relationship there so multiple values would be returned. But actually, I now managed to solve this problem and can share the resolution here if somebody else comes up with a similar problem. 

So I basically created a new measure that is the latest purchase qty and saved that to a variable. Then I calculate the corresponding quantity from the trade agreements table which is the biggest qty number that is still lower than the sales qty. Then just calculate the price for where the quantity is the same as we have in above explained variable and return the value.

Trade agreement price = 
VAR latest_qty = [Latest purch qty]
VAR corresp_qty =
    CALCULATE (
        MAXX ( TradeAgreements, TradeAgreements[QUANTITYAMOUNTFROM] ),
        TradeAgreements[QUANTITYAMOUNTFROM] <= latest_qty
    )
VAR corresp_price =
    CALCULATE (
        MINX ( TradeAgreements, TradeAgreements[Price Eur] ),
        TradeAgreements[QUANTITYAMOUNTFROM] = corresp_qty
    )
RETURN
    corresp_price