Relationships with unpivoted data

[Anonymous]

Hope I picked up the right forum as this is not specific to Power BI but more to vertipaq engine.

Sounds like a trivia or a quiz but it actually is a real problem I don't know how to solve in a vertipaq (Excel/PBI) data model: how can I end up with the desired output when having multiple mappings for one item (in that case one account->multiple reps) as in source data?

Here's a visual example of the data:

Now, of course the challenge is with table 4 as it doesn't contain any field with unique values (each rep can be mapped to multiple accounts and an account can have multiple reps).

I tried a couple of things (ex. establishing intermediate rel-tables with unique values as stated in the relationships' list) but none worked. so... external brains and knowledge needed 😋

Thx!

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please try

Total Amount =
CALCULATE (
SUM ( Table1[Amount] ),
CROSSFILTER ( Table1[Account ID], Table3[Acount ID], BOTH )
)

[Anonymous]

thx! You make me realize I haven't been prescriptive enough in my description: I need this to work at the relationship leve; that's an example, the model will contain many tables with many dimensions and many measures and users have to be able to create whatever view they may require: can't creative a measure for every possible scenario.

[tamerj1]

@Anonymous 

Either tables need to be combined or the relationship need to be two way. Seems to me both opinions are not possible in your case which leaves you with the DAX solution