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.