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.

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/

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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