Lookup another table with Multiple Values, if one value is correct report back

[Rogerh]

Good morning all,

I need some help please! I have two tables, Orders and Product Builder; The order table has a single line for each order. The Product table is the order broken down by each line item. Showing the items, supplier est. These are linked by the Order ID. The Product Table has the order ID multipul times if there are multipul items on the order.

From the Order table, I want to use the Order ID to look at the Product Table, check the supplier IDs and if one of them is 987 I want to report back on the orders table "Yes" or something simular under "delivery charge". 

Example

Order Table

Order ID    Delivery Charge?

123            I want this to say "Yes" as 987 is one of the suppliers

124            I want this to say "No" as 987 is not one of the suppliers

125            I want this to say "Yes" as 987 is one of the suppliers

Product Builder

Order ID   Supplier

123           987

123           897

124           789

124           788

125           987

125           987

I have tried this with a lookup but it will not work as i am looking up multipul values. Is there another way around this anyone can help with please?

Thanks

[amitchandak]

@Rogerh , Create a new column

if(isblank(countx(filter(Table,[Order ID] =earlier([Order ID ]) && [Supplier] =987),[Supplier])),"No","Yes")

[Rogerh]

Hi @amitchandak 

Thank you for your reply, i have tried your code but I can not get it to work.

Where you reference the table below (after filter), is that the order table or the product? Could you also confirm which table each of the order_ids come from? Thanks

[Pragati11]

Hi @Rogerh ,

I am assuming that both your tables are having a relationship on "ORDER ID" column.

You can try something as below:

NewDeliveryCol =

Var lookup = CALCULATE(VALUES('Product Builder'[Supplier]), FILTER(Product Builder, Product Builder[Order ID] = Order Table[Order ID]))

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

I haven't checked the syntax of above DAX, so let me know what output it gives and if there are any errors then what errors.

Thanks,

Pragati

[Rogerh]

Hi @Pragati11 

Thank you for your reply, I have tried the below but I get an error: A Table of multipule Values was supplier where a single value was exspected. Do you know how i can get around this please?

I updated your code to the below:

Delivery Charge = Var lookup = CALCULATE(VALUES('Platform - Pro Builder'[supplier_id]), FILTER('Platform - Pro Builder', 'Platform - Pro Builder'[order_id] = 'Platform - Orders'[order_id]))

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

Thanks

[Pragati11]

Hi @Rogerh ,

Try modifying the dax as follows:

Delivery Charge = Var lookup = LOOKUPVALUE('Platform - Pro Builder'[supplier_id], 'Platform - Pro Builder'[order_id], 'Platform - Orders'[order_id])

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

This should resolve the issue.

Thanks,

Pragati

[Rogerh]

Hi @Pragati11 

Thanks again but i am getting the same error - A Table of Multiple Values was supplierd where a single value was exspected.

I think this is due to the Pro Builder Table having the Order_ID multipul times.  Is there another way around you can think of please? Cheers

[Pragati11]

Hi @Rogerh ,

Try something like this:

Delivery Charge = Var lookup = CALCULATE(FIRSTNONBLANK('Platform - Pro Builder'[supplier_id], 1), FILTER(ALL('Platform - Pro Builder')'Platform - Pro Builder'[order_id] = 'Platform - Orders'[order_id]))

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

Thanks,

Pragati

[Rogerh]

Hi @Pragati11 

Thank you! Will this work?

Platform - Orders			Column We are working on
Order_ID			Delivery Charge
123			Yes
124			Yes
125			No

Platform - Pro Builder
Pro_Builder_ID	Order_ID	Supplier_ID
455	123	987
454	123	974
453	123	985
452	124	987
451	124	987
450	124	954
449	125	974
448	125	985

[Pragati11 · Accepted solution]

Hi @Rogerh ,

I think you missed my last response. Try using following dax:

Try something like this:

Delivery Charge = Var lookup = CALCULATE(FIRSTNONBLANK('Platform - Pro Builder'[supplier_id], 1), FILTER(ALL('Platform - Pro Builder')'Platform - Pro Builder'[order_id] = 'Platform - Orders'[order_id]))

Var checkVal = 987

RETURN

IF( lookup = checkVal, "YES", "NO")

Thanks,

Pragati

[Rogerh]

@Pragati11  That worked 🙂 thank you very much for your help