Many to many relationship workaround

[R_LB]

Hello PBI community, 

I have a simple problem around many to many relationship that i'm not able to solve. Please find below a simplified illustration of the problem :

Fact Table

Financial Item	Amount
Revenue	100
Revenue	80
Cost of Sales	-20

P&L Layout

P&L Structure	Financial Item
Revenue	Revenue
Cost of Sales	Cost of Sales
Gross Profit	Revenue
Gross Profit	Cost of Sales

I want to create a recap of the Amount by the field "P&l Structure". 

Output

P&L Structure	Amount
Revenue	180
Cost of Sales	-20
Gross Profit	160

To make that, i have a many to many relationship betwenn the two table on the column Financial Item.

-> I would like to find a way to model that in order to delete my many to many relationship without using DAX if possible.

I would be very glad if someone can help.

Kind regards

[R_LB]

Hello @lbendlin , thanks for the reply. However, the example you provided has a many to many relationship, which is exaclty what I want to avoid 😁.

[lbendlin]

And why would you want to avoid it?

An alternative would be to not use a data model (ie use disconnected tables) and LOOKUPVALUE or TREATAS. I would consider these inferior.

[R_LB]

I want to avoid it for performance issue. I also want to avoid TREATEAS or LOOKUP for the same reason. I gave a little example example but in reality, I have a model with around 80 tables, hundreds millions of rows in the fact table, so many to many relationship has to be avoid as much as possible.

[lbendlin]

There is no such thing as free lunch. Somewhere you will have to do the computation (and absorb the cost) - either in Power BI, or in the upstream system.  The further upstream you do it the more you lose flexibility.

Keep in mind that Vertipaq runs in memory.  Even with millions of rows performance should be acceptable.

[lbendlin]

see attached