topic Lookup Function not return the value in DAX Commands and Tips

Lookup Function not return the value

Anonymous — Thu, 05 Jan 2023 15:08:02 GMT

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 .

Re: Lookup Function not return the value

Kriekis — Thu, 05 Jan 2023 15:17:55 GMT

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

Re: Lookup Function not return the value

Anonymous — Fri, 06 Jan 2023 02:43:58 GMT

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