cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rogerh
Helper II
Helper II

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

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

 

1 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

My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

@Rogerh , Create a new column

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
Super User
Super User

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

 

 

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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

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

My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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

 

 

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

My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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_IDOrder_IDSupplier_ID
455123987
454123974
453123985
452124987
451124987
450124954
449125974
448125985

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

My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!