How to combine 2 tables with a many-to-many relationship

[sol3nsa]

Hi Everyone, I keep thinking I've got the answer and then find a bizarre set of results which proves I haven't.

How do you combine 2 tables as follows:

Such that the following calculation is done:

Proj A = 0.3(1+2) + 1(3) + 0.2(2+3+4)
Proj B = 0.7(1+2) + 0.8(2+3+4)

To get: 

I've set it up as a many-to-many relationship but I can't get the table visual to show this result correctly.

The tables are greatly simplified, they're actually 10s of 1000s of rows big.

[lbendlin · Accepted solution]

Here is your ideal data model with three dimensions and two facts.

Unfortunately this won't get you from the course hours to the project hours. 

You have a couple of options here, for example you could make the cross filter direction bidirectional

But that won't help with the calculation

So the next best option is a measure.

Hours = 
var a =  ADDCOLUMNS('Project User Allocation',"uh",var u=[User ID] return CALCULATE(sum('User Course Hours'[Hours]),'User Course Hours'[User ID]=u))
return sumx(a,[Headcount]*[uh])

There are other options but they won't be as good 🙂

See attached.

[sol3nsa]

Many thanks Ibendlin - that's super