Linking BOM Header to First item in Production BOM

[Boing1968]

I have a very simple BOM header - Production BOM set up where the major component for all parents is made from the item on first line of the production BOM. I would like to add an is made from field so that every production bom header is populated with its major component. I am trying to use LOOKUPVALUE to get this data, and have tried several methods but keep running into the old chestnut "A table of multiple values was supplied where a single value was expected." I am new to DAX and already have no hair!! finding this message a little frustrating as i have tried several approaches, the simplest of which is this. 

Column = lookupvalue('Navision$Production BOM Line'[No_],'Navision$Production BOM Line'[lookup],'$NavisionProduction BOM Header'[lookup])

The lookup field = concatenate(Bom_no),"10000" to select first line 

The data in every lookup field is unique, am struggling to understand why is doesnt work. Any pointers would be greatly appreciated.

[Boing1968]

I have seen the error of my ways and now have it working......

[Anonymous · Accepted solution]

HI @Boing1968,

This issue will appear when your expression gets multiple returns, you can set 'Alternate Result' with the aggregation function to prevent this:

Column =
LOOKUPVALUE (
    'Navision$Production BOM Line'[No_],
    'Navision$Production BOM Line'[lookup], '$NavisionProduction BOM Header'[lookup],
    MAX ( 'Navision$Production BOM Line'[No_] )
)

Notice: Calculated column does not interact with filter effects, if you want to get the dynamic result, please use the measure expression instead.

Regards,

Xiaoxin Sheng