Forum Discussion
Modeling and Calculations
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.⚡
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!
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.⚡
hitesh160790 one quick note here, you said, you create the relationship to both to make it work, which is fine but in general/as best practice, avoid using the relationship to both because, on larger/complex DAX expression, it can have performance implications and can also lead to some unexpected results because of cross filter direction to both. I have used the same both but in the measure so that it is only used when it is relevant.
Just sharing what is the best practice. It is not about solving a problem, it is how to follow best practice and make a scalable solution. Good luck!