Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

lOOKUP WITH MULTIPLE CONDITIONS

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      

"Need Help"
@dax @help
@Please Help HELP
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous  thank you so much  its working 

  • SamInogic's avatar
    SamInogic
    Super User
    3 years ago

    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 [email protected]

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

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