Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
@Rogerh , Create a new column
if(isblank(countx(filter(Table,[Order ID] =earlier([Order ID ]) && [Supplier] =987),[Supplier])),"No","Yes")
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |