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.
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.
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/
You got a 100.00% upvote from @spydo courtesy of @theexcelclub! We offer 100% Payout and Curation. Thank you.
all of the data where I work ends up many to many, so I use bridging tables a lot and it brings all the benefits of being able to analyze different tables against each other. In power bi you can use bi-directional filtering and relationships but lets not confuse power pivot users. Of all the tricks you shared, learning to use CALCULATE is probably the most important, its just the magic of magic.
Thanks for this post @theexcelclub and the chance to do these learn and earn activities. Im hoping to earn enough steem from them to buy your premium courses. this is such a fantastic idea.
CALCULATE rocks and when I am doing some DAX posts, I will for sure cover it more. the rule of thumb, if it doesn't work, wrap calculate around it and most of the time it will solve your problem :-)
As always Paula this is a great resource, when I started out on powerpivot I always got that 'Relationships may be needed' warning and could never figure it out because it would not let me set up the links. Now after using Power pivot for so long its natural for me to work with bridging tables.
Nice sharing of workbooks to practice with. Cheers!
hi @dernan, I'm glad you enjoyed the post. thanks for taking part in the learn and earn activity
Thank you for the tutorial paula.
I would be lost without you.
you are most welcome
Very cool.
I had to have a go at this one because I was hopeful there would be a way of having the Date/Product joined as a composite key. Apparently not!
This is a decent workaround though, thanks Paula!
oh that's an interesting take, it makes a lot of sense and sounds almost plausible - to the extent you have given me an idea!!!!
Can you make derived tables?
So the join, or foreign key table would be 'date'+'product' - 60 rows if there were 15 dates and 4 products. Dunno, other examples i've seen online use the same approach as you :)
its works in sql and I think with a little DAX magic you could get it to work.
When I was hunting for help I saw DAX mentioned but you know, I got things to do and people to see :P