Forum Discussion
Modeling and Calculations
I have 3 tables
1) Product
| Product Id | Name |
| 1 | ABC |
2) Components
| Product ID | R Name | Component Name |
| 1 | Recipe | Ing A |
| 1 | Recipe | Ing B |
3) Future Plan
| Product Id | Buy |
| 1 | 500 |
Now we have
1 to many relationships for Product and Components
and 1 to many relationships for Product and Future Plan
I am trying to create a calculation so that I can get
| Product Id | R Name | Buy |
| 1 | Recipe | 500 |
but I am getting the wrong results. Can anyone please help me out here.
Create below relationship :
Component Table --> Future Plan (one to many based on ProductID with corss filter direction "Single")
Future Plan --> Product Table (One to one based on ProductID with cross filter direction "Both")
hitesh160790 In your original post the relationship was good. Although Tahreem24 solution would have worked given the sample data I think you need to keep the same relationship in and use the measure to do the job.
I have put together sample data of 2 products and multiple recipes for one of the product and multiple buys and everything should work.
See attached, it contains many tables but just focus on Product/Receipt and Product Future table and a measure in the product table. You can tweak it from here.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
8 Replies
Create below relationship :
Component Table --> Future Plan (one to many based on ProductID with corss filter direction "Single")
Future Plan --> Product Table (One to one based on ProductID with cross filter direction "Both")
Thanks a lot, it worked. Just a small change I did,
Component Table --> Future Plan (one to many based on ProductID with cross filter direction "BOTH") was used here instead of another table.
Thank you
Hi Tahreem24 - thank you for your reply. The only issue with this model is that there is 'one to many' relationship between Future Plan and Product. The future plan is based on every month.
Like for Product 1,
July 300
June 200
I want a total of 500 here which can be seen in the final table.
Wait, you didn't mention about Month in Future plan table. Kindly share the proper details. Based on your input I have created sample at my side. Please check my relationship image and let me know the if I missed any column.
I would really appreaciate your KUDOS/THUMBS UP for support!
