Forum Discussion
Get a corresponding price from another table using relationship through item table
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 3vDimItem:
ID ITEMID
1 1234
2 1235
3 1236TradeAgreements:
ITEMRELATION FROMQTY AMOUNT
1234 0 1
1235 0 5
1235 10 2
1236 0 10
1236 5 5
1236 10 2Goal/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
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
2 Replies
- Jayee
Responsive Resident
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!!
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
