Many to Many Relationship

Anything related to PowerPivot and DAX Formuale
Posts: 5
Joined: Mon Feb 25, 2019 5:31 am

Many to Many Relationship

Postby Franz » Fri Mar 15, 2019 8:18 am

Dear community

I got stuck with a many-2-many relationship ...

I have three tables
tables.png (13.57 KiB) Viewed 224 times

I googled and got an idea how to link my p&l table to my functions table - in general.
However, I don't just want to filter my p&l by functions I also want to allocate the values in the p&l to the functions i.e. multiply the two values. And that's were I got stuck.

And as an add-on: whenever I tried so solve the issue, I got the yellow warning message "relationships between tables may be needed". How do I need to set it properly to avoid this warning?

Any help is highly appreciated.

(225.74 KiB) Downloaded 9 times

Posts: 235
Joined: Tue Sep 09, 2014 8:13 am

Re: Many to Many Relationship

Postby PhilC » Tue Mar 19, 2019 12:30 pm

Hi Franz,

I'd expect there is a better approach to this, but in lieu of someone else helping out you could try the attached.

I have used Power Query to import the data and process it, before putting it into the data model. I'd suggest you always use Power Query to get data into the model rather than linking files or tables directly as it gives you far more flexibility if you want to change aspects down the road (and I am sure you will find you do as you continually improve a model).

My approach may provide a performance issue if the dataset is large, as I am expanding the PL table by every corresponding Cost Object value from the Functions table.

If you want a more elegant/optimised solution, people on the Power BI Community forums might be a good place to ask for a better solution.

Many2Many PPM.xlsx
(320.35 KiB) Downloaded 7 times

Posts: 5
Joined: Mon Feb 25, 2019 5:31 am

Re: Many to Many Relationship

Postby Franz » Tue Mar 19, 2019 6:01 pm

Hi Phil

Many thanks for your suggestion.
Originally I was thinking in the same line but also got sceptical in regards to the performance issues.
In the meantime I found a method/solution in an article of Matt ( ... explained/).
The (new) issue I have is that for some records Matt's measure does not give the right result - and I have absolutely no clue why.

Reading your suggestion this morning I thought that you are right. I should solve it via PQ. It impacts the performance but it solves the issue ... at least until I find a solution to the DAX measure problem.

Many thanks again.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests