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 loca...
"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.

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