topic Re: Measure to SUM secondary column value reference, ignoring other filter context in DAX Commands and Tips

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

drbr — Wed, 16 Dec 2020 10:14:45 GMT

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_STOCK

Final_Item_BOM

AlB — Wed, 16 Dec 2020 10:29:12 GMT

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 — Wed, 16 Dec 2020 10:54:56 GMT

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?

AlB — Wed, 16 Dec 2020 11:04:15 GMT

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 — Wed, 16 Dec 2020 11:43:32 GMT

Thank you!
Simple, yet effective! 👍