Modeling and Calculations

[hitesh160790]

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.

[Tahreem24 · Accepted solution]

@hitesh160790 ,

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")

[Tahreem24]

@hitesh160790 ,

Please refer the below Screen shot and try to implement this.

[Tahreem24 · Accepted solution]

@hitesh160790 ,

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]

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

[hitesh160790]

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.

[Tahreem24]

@hitesh160790 ,

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!

[parry2k · Accepted solution]

@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.⚡

[parry2k]

@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!

[hitesh160790]

Thank you @parry2k for explaining this in detail.