topic Get a corresponding price from another table using relationship through item table in DAX Commands and Tips

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

Anonymous — Tue, 22 Nov 2022 12:38:55 GMT

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

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

Jayee — Tue, 22 Nov 2022 14:55:40 GMT

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!!

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

Anonymous — Tue, 22 Nov 2022 15:18:51 GMT

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