Measure to SUM secondary column value reference, ignoring other filter context

[drbr]

I have a challenge to create a measure for scenario bellow, without changing the data model.

I know I could add an extra duplicate Stock table with proper relation, but I would like to solve it with a measure.

Thanks for any suggestion.

Tables:

Relations

Item_DIM

Item_DIM	ItemType_DIM
Item 1	Final
Item 2	Final
Item 3	Raw
Item 4	Raw
Item 5	Raw

Item_STOCK

Item_STOCK	ItemQty_STOCK
Item 1	10
Item 2	20
Item 3	30
Item 4	40
Item 5	50

Final_Item_BOM

Item_BOM	ItemChild_BOM	Qty_BOM
Item 1	Item 3	0,2
Item 1	Item 4	0,3
Item 1	Item 5	0,4
Item 2	Item 3	1,2
Item 2	Item 4	1,7

[AlB · Accepted solution]

@drbr 

Measure =
VAR aux_ = SELECTEDVALUE ( FINAL_Item_BOM[Item_BOM] )
RETURN
    LOOKUPVALUE ( Item_STOCK[ItemQty_STOCK], Item_STOCK[Item_STOCK], aux_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[drbr]

Thank you!
Simple, yet effective! 👍

[AlB]

Hi @drbr 

Measure =
VAR aux_ = SELECTEDVALUE ( Item_STOCK[Item_STOCK] )
RETURN
    LOOKUPVALUE ( Item_STOCK[ItemQty_STOCK], Item_STOCK[Item_STOCK], aux_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[drbr]

Thank you very much.

Measure unfortunatelly does not return proper vaules.

It is a little more complex, as LOOKUPVALUE is not Item_STOCK item, but item from it's father on Final_Item_BOM?