Many to many relationship

[Anonymous]

Hi,

I have a model where I have a many to many relationships in my model. 

I need to created a calculated column which uses columns columns from different tables in the model. I am not being allowed to use the 'related' function here as I have many to many relationships defined in the model. How can I create something like - s.FREQUENCY*lc.time*m.input_value as totalHours (s = service matrix table, lc = labor class table abd m= mission user input table). Attached is the screenshot of the model and the tables associated. Any ideas will be appreciated!!

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create a calculated table Intermediate_table to union the table lc and table m to get distinct task_id , and create relationships between the three tables like picture below. Then change the Cross filter direction of relationships between the tables in your screenshot from Single to Both, not only the table s and table lc but also the other tables (recommend this for simplifieding the data model or further calculation), which will take them treated as a single table.

Intermediate_table = DISTINCT(UNION(SELECTCOLUMNS(lc,"task_id",lc[task_id]),SELECTCOLUMNS(m,"task_id",m[task_id])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xicai · Accepted solution]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create a calculated table Intermediate_table to union the table lc and table m to get distinct task_id , and create relationships between the three tables like picture below. Then change the Cross filter direction of relationships between the tables in your screenshot from Single to Both, not only the table s and table lc but also the other tables (recommend this for simplifieding the data model or further calculation), which will take them treated as a single table.

Intermediate_table = DISTINCT(UNION(SELECTCOLUMNS(lc,"task_id",lc[task_id]),SELECTCOLUMNS(m,"task_id",m[task_id])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.