Multiply two tables with a many to many relationship

[Anonymous]

I want to multiply Split by the Volume on the match of Hopital and Pathway. The added issue I have aswell is the both tables are a many to many relationship on Hospital and Pathway, which I can resolve by creating reference tables and joining both tables to them.

I get results with 

Volume Split by Overnight = SUM(Activity[Volume])*SUM(Hospital[Split])

But I get the incorrect total.

Here is the tables;

Relationships to resolve many to many;

Thank you in advance!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Based on your steps, please create the measure below to achieve your desired output.

Measure =
VAR _table =
    FILTER (
        GENERATE ( Activity, Hostipal ),
        Activity[Hospital] = Hostipal[Hospital]
            && Activity[Pathway] = Hostipal[Pathway]
    )
RETURN
    SUMX ( _table, [Volume Split by Overnight] )

Here is the output.

Best  Regards,

Cherry

[fluppo]

Dear Cherry

I have a very similar problem like JayZee64.

Many-to-Many-Relationship

Applying your solution I noted that it took a bit of time to calculate the measure with a mid-sized table.

Does the below method also work well with large tables?

Are there alternative options you would recommend besides the _table on the fly?

Many thanks in advance

F.