many to many relationships power pivot

Easy Excel Solutions: Power Pivot Many to Many Relationships

Learn How to Manage Many to Many Relationships in Power Pivot

Power Pivot has been around since 2010, and many people still struggle with managing relationships.  Even more so most struggle with more complex relationships such as Many to Many relationships.  In fact, I have seen many people begin to set up data models in Excel, only to throw the hat in because they don’t know what to do when a many to many relationships exists.

Relationships define the backbone of any data model set up in Excels Power Pivot.  Their existence enables us to carry out calculations and analysis across multiples tables of data.

Relationship connect two or more tables of data together.  To connect each table, both tables must have something in common.  There are two types of relationship, a one to many which works very well in Power Pivot and DAX.  Then there are Many to Many relationships, which can cause problems. I have even heard people say, Power Pivot does not support Many to Many relationships.

In this video we will look at three many to many relationship problems and how you can easily overcome them.  The approach we will take is very systematic.  We will build up our data model table by table.  This way we can see how additional tables of data, that contain many to many relationships, can effect our pivot table formula. Then we will look at how the problems can be solved.

The data set is available at the bottom of the page to practice along, scroll down under the video.


Do you want Excel and Power Excel Tips to your inbox?  Sign up now to our newsletter

Found this video and article of use?  Please do share it with your friends and colleagues

This blog is powered by the STEEM blockchain using as the integration tool. To comment on this post you must have a STEEM account.  You can use this link to register for a STEEM account.

If you wish to practice along with this video, you can download the data set here.

%d bloggers like this: