Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to Solution.
Hi @Rogerh ,
I think you missed my last response. Try using following dax:
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
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
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:
Hi @Rogerh ,
Try modifying the dax as follows:
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 ,
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 |
Hi @Rogerh ,
I think you missed my last response. Try using following dax:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
62 | |
46 | |
45 |