Multiplying columns from indirectly related tables - result as a calculated column

[1up]

Hi,

The goal is to create a calculated column in Table A.

It should be the result of multiplying column values row-by-row from two indirectly related tables "Table A" & "Table C".

Example:

"Table A" is related to "Table C" indirectly via "Table B".

They all share the same key column "ID".

Let's say Table A has prices, Table C has quantities. Multiplying the two for each matching ID-kolumn would provide volumes.

Table relations are One-to-Many

Filter direction is currently as per below:

Table A -> Table B -> Table C

Is this doable?

I have tried different approaches, however I get stuck. I'm not sure if there is a solution for the existing data relationship model, or if adjustments are needed in it as well first. Either on filter direction. Or perhaps a thought has been to create a relation between Table A directly with Table C, but then I get a Many-To-Many-relationship.
So it has some complexities, naturally. 🙂

If anything is unclear, please let me know.

Thanks for help,
// Daniel

[1up · Accepted solution]

Thanks for the reply, JK.

The solution is to make sure the relations are 1-many. In our model, we had one many-to-many relation for one of the tables involved in this calculation, which isn't supported / doesn't work.

Please find below as guidence for anyone else who is wanting to create a calculated column including indirect relations to other tables.

1) Decide in which table the calc column is most suitable to be created.

2) Verify the relationship-model if any many-to-many-relationships exist for concerned tables.

2a) If it does, create an additional so called "bridge table" to get 1-many relations only. It is needed to delete existing relations with the many-to-many table, and re-create them towards the bridge table.

3) Depending on your use case, consider using the RELATED, RELATEDTABLE, USERELATIONSHIP functions of Dax.

4) Since calculated columns can slow down a report, to speed it up, consider starting the Dax expression with a filter if possible, and then the calculated column expression, so to only calculated values for rows where really needed.

5) If speed is still an issue, consider an implemention upstream, at the source-side, at the database-side as an example.

[v-jianpeng-msft]

Hi, @1up 

The workaround you shared is really good and I appreciate your sharing. You can mark your reply as a solution, so that when other members of the community search for related issues, your solution can be quickly searched, thank you again for sharing.

Best Regards

Jianpeng Li

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

[1up]

Thanks for the reply.

I found out that one of the indirectly related tables is having a many-to-many-relation, which then isn't functioning together with the related-dax function to my understanding.

We may choose to perform the calculation in another way to solve this.

[v-jianpeng-msft]

Thank you Jihwan_Kim 

Hi, @1up 

You have solved the current problem. You can mark your response as a solution so that other members of the community can quickly find an answer if they have a similar problem. Thank you for your cooperation, and your sharing will work with me to build a better community environment.

Best Regards

Jianpeng Li

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

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try using RELATEDTABLE DAX function in your calculated column formula when creating calculated column in table A.

RELATEDTABLE function (DAX) - DAX | Microsoft Learn

[1up]

Hi,

Yes I have tried and tested the relation-related dax functions; RELATED, RELATEDTABLE. They work well with directly related tables. For indirectly related tables, I haven't gotten it to work. The issue being that the destination or indirectly related table is not appearing as an option when writing the dax statement.

I am not sure if this relate to the relationship model itself, or that the dax code should be different. Some tutorials suggest using the FILTER-function in combination with a relation-dax function like RELATED. But I didn't get that to work.

Further assistance from anyone would be much appreciated.
I think one of the challenges is the indirect relationship, the second may be how the relation is, or the filter direction.

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link (onedrive, googledrive, dropbox, or other methods...), and then I can try to look into it.

Thank you.