Multiplying across numerous tables

[AndyTrezise]

My data a model that includes three tables which are all linked as follows:

Projects -> Tasks -> Assignments

Multiply Tasks per project, multiple assignments per task.

I need to arrive at a value which is the result of multiplying a filed from each table – Projects.Column1 * Tasks.Column1 * Assignments.Column1

I’ve managed to do it by creating new columns using LOOKUPVALUE so all the values are in the same table – I can then create a further column to multiply them out.

However I’m sure there must be a single step way of multiplying these value across the 3 tables??

[spuder · Accepted solution]

Hi @AndyTrezise

I'm not absolutely sure. But you could try to use

=SUMX(PROJECTS;'Projects'[Number]*RELATED('Tasks'[Number]*RELATED('Assignments'[Number]))

Note: This is only vailid when ONE Project has ONE Task and ONE Assignement. In fact 1:1 relationship.

Greetings spuder

[v-chuncz-msft]

@AndyTrezise,

When using a measure, you may also take advantage of CROSSFILTER Function to set cross-filter direction to both.

[Phil_Seamark · Accepted solution]

Hi  @spuder

It should be possible to multiply across three tables without using the LOOKUPVALUE column.

If you create a calculated column in your Assignments table you can use a calculation like this

Column = RELATED('Projects'[Val]) * RELATED('Tasks'[Val]) * Assignments[Val]

This can also be done using a measure, or as a column in one of the higher tables, but you'd need to use RELATEDTABLE and SUM to complete.

[spuder · Accepted solution]

Hi @AndyTrezise

I'm not absolutely sure. But you could try to use

=SUMX(PROJECTS;'Projects'[Number]*RELATED('Tasks'[Number]*RELATED('Assignments'[Number]))

Note: This is only vailid when ONE Project has ONE Task and ONE Assignement. In fact 1:1 relationship.

Greetings spuder