Lookup Function not return the value

[Anonymous]

I have the two table Global parameter and Remark2(local) . i am trying to get BOM value from Remark2(BOM) in Global parameter table  using  the below 

Lookup val-Remark 2 - BomText =

 //CALCULATE (

   // FIRSTNONBLANK ( 'Remark2 (local)'[BOM Text (Parameter BOM)], 1 ),

    //FILTER ( ALL ( 'Remark2 (local)' ), 'Remark2 (local)'[Number] = 'Global Parameters'[Item Code] )

//)

CALCULATE(SELECTEDVALUE('Remark2 (local)'[BOM Text (Parameter BOM)]),FILTER(ALLNOBLANKROW('Remark2 (local)'[ItemCode]),'Remark2 (local)'[ItemCode]=='Global Parameters'[Item Code]),ALL('Remark1 (local)'))

But Lookup function dosn't work . i have attached  power bi file reference  Power BI File  

Global Parameter :

ItemCode                       Rev                    Outer

FA031375.12                    11                  Thenna

FA031375.13                      9                  Thenna

Remark2 (Local)

ItemCode                         Rev            BOMTEXT(Parameter BOM)

FA031375.12                    11                  Thenna

FA031375.13                      9                  Thenna

Execpted output:

ItemCode                       Rev                    Outer       BOMTEXT

FA031375.12                    11                  Thenna       Thenna

FA031375.13                      9                  Thenna       Thenna

Looking for support . thanks in advance .

[Anonymous · Accepted solution]

@Kriekis  after the changes Relationshiop it is working . Thank you.

[Kriekis]

Hi,

You can use dax expressions below.

But in general Your model is good example which shows that usage of many-to-many relationships isn't good idea.

So many-to-many is the main problem here.

Lookup val-Remark 2 - BomText =

VAR _itemCode = 'Global Parameters'[Item Code]

RETURN

CALCULATE(

  LOOKUPVALUE(

    'Remark2 (local)'[BOM Text (Parameter BOM)],

    'Remark2 (local)'[Number],

    _itemCode

  ),

  CROSSFILTER('Remark2 (local)'[Item Code], 'Remark1 (local)'[Item Code], None) // disables relationship

)

/Andris