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 exist.

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.

Relationships 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 work 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. This is not true. There is an easy workaround and today you will learn how you can overcome and manage many to many relationships in Power Pivot.

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 affect our pivot table formula. Then we will look at how the problems can be solved. You will be introduced to bridging tables and you will also be introduced to the DAX function Calculate.

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

Do you want Excel and Power Excel Tips to your inbox? Sign up now to our newsletter and never miss an Excel, PowerBI or DAX Learn and Earn Activity again.

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

Learn and Earn Activity - Many to Many Relationships in Power Pivot

First, download the data set here. Then, set up the same relationships structure as shown in the video. To earn tokens for this activity, in the comments section below answer the following questions

From the 3 many to many relationships problems shown in the video, which one would you encounter the most?
Now that you understand how to set up a bridging table, where do you think you could use this in your organization when analyzing data and what benefits will it bring?
Share any tips you might have when working with relationships in Power Pivot.
Post any questions/comments or feedback you might have on the above video.

Now there is value in Learning with The Excel Club and Learn and Earn activities.

We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.

Click Here to find out more now and start earning cryptocurrency tokens while you are learning Excel and Power BI

Cross posted from my blog with SteemPress : https://theexcelclub.com/power-pivot-many-to-many-relationships/