lOOKUP WITH MULTIPLE CONDITIONS

[THENNA_41]

I have two table Global file and local file .  both tble have Have common value Itemcode  . based on the Itemcode trying to lookup Reference Bom .If the Item is not found in the Reference BOM in local and if the global BOM column “D” Item (Product Variant BOM) contains  Alphanumeric values Example FAXXXXX.XX-SHIPPINGCASE-CAS  then we checked the Item type in  local  file conations the "Shipping Case - 70 "  then  pick  the reference bom for the shipping case -70 to use.

Item (Product Variant BOM)                                Item Type

FAXXXXX.XX-SHIPPINGCASE-CAS	Shipping Case - 70
FAXXXXX.XX-STICKER-PAK	Sticker - 45
FAXXXXX.XX-CASELABEL-CAS	Shipping Case Label - 71
FAXXXXX.XX-CASETAPE-CAS	Shipping Case Tape - 72

Global File

ItemCode                    Item (Product Variant BOM)

FA009644.07             FA009644.07-SHIPPINGCASE-CAS

FA009644.07             FA009644.07-CASETAPE-CAS

FA009645.08             FA009645.08-SHIPPINGCASE-CAS   

Local File :

ItemCode                    ReferenceBom                                                         ItemType                   

FA009644.07             FA009644.07-CASETAPE-CAS                                 Shipping Case Label - 71    

FA009645.08             FA009645.08-CASELABEL-CAS                               Shipping Case Tape - 72    

 A009645.08                              70.X223                                                   Shipping Case - 70  

Expected Output:

ItemCode                    Item (Product Variant BOM)                             ReferenceBom   

FA009644.07             FA009644.07-SHIPPINGCASE-CAS                           70.X223      

FA009644.07             FA009644.07-CASETAPE-CAS                              FA009644.07-CASETAPE-CAS

FA009645.08             FA009645.08-SHIPPINGCASE-CAS                            70.X223      

[v-xinruzhu-msft · Accepted solution]

Hi @THENNA_41 

You can try the following calculated column:

Column = var _lookup=FILTER('local BOMs','local BOMs'[Item Code]=EARLIER([Item Code])&&[Reference (BOM)]=EARLIER([Item (Product Variant BOM)]))
var _value=SUMMARIZE(_lookup,[Reference (BOM)])
return IF([Item (Product Variant BOM)] in _value,[Item (Product Variant BOM)],IF(CONTAINSSTRING(LEFT([Item (Product Variant BOM)],2),"FA"),MAXX(FILTER('local BOMs','local BOMs'[Item Type (BOM)]=EARLIER([Item Type (Product Variant BOM)])),[Reference (BOM)])))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[THENNA_41]

@v-xinruzhu-msft  thank you so much  its working 

[SamInogic]

Hi,

We are not sure we got your query completely, but as per our understanding, you want to display data with specific Multiple Condition on Lookup Value functions.

This our sample table as your Data as shown in below screenshot.

We have used below DAX expression in My Sample Tables (Tables has been created as per your Data).

Firstly, have created a column [ItemType Lookup] that returns Item Type based on Item Code from Local File table

ItemType Lookup = LOOKUPVALUE('Local File 1'[ItemType ],'Local File 1'[ItemCode],[ItemCode  ])

Now will add another custom column Reference BOM with below DAX expression,

Referecne BOM = IF(ISBLANK(LOOKUPVALUE('Local File 1'[ReferenceBom],'Local File 1'[ItemCode],[ItemCode  ])) && ISERROR(VALUE([Item (Product Variant BOM)])) &&  LOOKUPVALUE('Local File 1'[ItemType ],'Local File 1'[ItemCode],[ItemCode  ])="Shipping Case - 70", [ItemType Lookup],LOOKUPVALUE('Local File 1'[ReferenceBom],'Local File 1'[ItemCode],[ItemCode  ]))

This Expression returns below result as expected,

Please check if provided solution works for your requirement otherwise you can provide sample PBIX file so that we can check and look into the same further.

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Services

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[THENNA_41]

@SamInogic  please find the power bi link for your reference Power BI FILE ..