topic lOOKUP WITH MULTIPLE CONDITIONS in DAX Commands and Tips

lOOKUP WITH MULTIPLE CONDITIONS

Anonymous — Thu, 12 Jan 2023 05:26:13 GMT

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

Re: lOOKUP WITH MULTIPLE CONDITIONS

SamInogic — Thu, 12 Jan 2023 08:53:32 GMT

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/

Re: lOOKUP WITH MULTIPLE CONDITIONS

Anonymous — Thu, 12 Jan 2023 09:49:20 GMT

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

Re: lOOKUP WITH MULTIPLE CONDITIONS

Anonymous — Fri, 13 Jan 2023 07:19:24 GMT

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.

Re: lOOKUP WITH MULTIPLE CONDITIONS

Anonymous — Fri, 13 Jan 2023 08:23:05 GMT

Anonymous thank you so much its working