Create a Many to Many Relationship Model in Power Pivot

If you're familiar with Power Pivot, you probably know that you can't create many-to-many relationships between multiple tables. But in real life there may be instances where we would want to manage many to many relationship to help answer some questions. This video give an example of a type of question that begs to be answer and uses Power Pivot and some DAX functions to help answer this question. Hint: It involves the use of a bridge table.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@joycelau3508]

I couldn't agree more with other positive comments below. This is by far the best explanation video. Thank you so much!!

[@derekgarner8524]

I can't thank you enough. I often needed some way to combine results by a time period or market segment and this solution is so easy and well illustrated. You have made my Friday. Thank you.

[@DougHExcel]

Thanks Derek Garner, glad it helped!

[@ovmov3321]

Man, you are the best really i spent so much time to figure it out and you here just in 10 min explain everything ...

[@DougHExcel]

Thanks OV MOV, glad it helped!

[@richardkorver7482]

Many...many thanks....to you and the man behind your source- blog!!!

[@DougHExcel]

Much appreciated!

[@jonathanniels]

I am going to give this a shot, it's been the best detailed explanation I found so far!

[@soloseven007]

Can't thank you enough! Been suffering with this issue for so long, and I'm so thankful to have found your help. Like & subscribe..Done!

[@DougHExcel]

Thanks soloseven007, glad it helped!

[@jwf3148]

An EXCELLENT tuturial - clear, organized, professional - AND - an effective learning tool...cannot ask for more than that !!! Ha !!!

[@DougHExcel]

Wow, thanks!

[@SEWEN7777777]

Helped me very much, ty

[@DougHExcel]

You’re welcome!

[@jcorrea]

Thank you very much!! very clear explanation!

[@DougHExcel]

You are welcome!

[@john4thelove]

I read that blog months ago and could not make it work, thank you this! It WORKS!

[@DougHExcel]

Thanks john4thelove, glad it helped!

[@Mephistopheles019]

Finally!! After searching for a very long time I finally stumbled on this video. You’ve increased my education bigtime with this one. Appreciate it.

[@DougHExcel]

Glad it was helpful!

[@gianniedgar8940]

you probably dont care but does anyone know a method to log back into an Instagram account..? I somehow lost the login password. I appreciate any help you can give me

[@haashini10031985]

Excellent Way of Teaching... Thank You

[@DougHExcel]

Thanks and welcome!

[@SyedMuzammilMahasanShahi]

Beautiful video. Thanks for the share.

[@DougHExcel]

Hi Syed Muzammil Mahasan Shahi, thanks for the comment!

[@KieuPhuongHoangDang]

thank you so much, it's so helpful and thanks again

[@johng5295]

Thanks in a million. Awesome!

[@DougHExcel]

You’re welcome!

[@tinocoan]

The calculation step solved my problem. Thank you so much!

[@DougHExcel]

Hi Antonio Matheus Tinoco, you're welcome!

[@ericgonzalez2292]

This helped a lot to resolve a problem I have had for many months now!! Thanks!

[@DougHExcel]

Glad it helped!

[@averageboy5]

This is solid! Great job on explaining DAX cross table filtering. I’d been spinning wheels more times than I can dare to count. I am now going to dazzle the masses, thanks to this!

[@DougHExcel]

Glad it was helpful!

[@averageboy5]

Btw, on close scrutiny, I don’t think you need that extra “unique customer” filter table. The unique product bridge table s/b sufficient to pull the correct totals. It puzzled me the first time but still great example!

[@info2satish]

OMG! You've just solved my problem. Love from India ❤️🙏👍

[@DougHExcel]

Glad it helped!

[@errolm8313]

Thank you!

[@DougHExcel]

You're welcome!

[@yabezrosy]

Thanks Dough H your video ended my 1 week search on the exact same issue I was having

[@DougHExcel]

Glad it helped!

[@heshamsaad9883]

It helps so much - Gazak Allah Khier - Allah will reward you

[@aluedemichael352]

This video hit the spot! great job!! however grand total of potential sales isn't accurate. how do you spin that? it has me stuck on me current work.

[@sergeyp6472]

Thanks a lot!!!

[@DougHExcel]

You're welcome!

[@mozhistmk]

OMG Awesome man.. you made my day ..! thanks a lot!

[@DougHExcel]

You’re welcome!

[@gborka]

thank you so much

[@DougHExcel]

You’re welcome!

[@jorisvanh.9274]

Thank you for the video. When applying slicers for let's say customer name, based on what table in the data model would you do this?

[@mau_vh]

Thanks man

[@DougHExcel]

You're welcome!

[@giordanirivas294]

Great solution for the dilemma of many to many relationships. My example is similar but the values repeated are percentages and I am not solving the problem with a sum formula (adding percentage is not correct). Any idea on a work around?

[@stevesullivan457]

Great job. Alas, my head is spinning lol

[@DougHExcel]

Hi Steve Sullivan, thanks for the comment!

[@shounaksen3336]

hahaha.. Same here

[@danjarupath]

Me too

[@Tat-cx1lr]

Merci

[@DougHExcel]

De rien!

[@largpack]

Why doesn't support Power Pivot this out of the box like Microsoft Power BI does? Thanks for the really helpful video!

[@hasinarafenoarisoa7063]

thank you so much for these videos! How to solve one to one relationship model in powerpivot, please?

[@DougHExcel]

Sorry, don’t understand the comment/ question... but it sounds more like a lookup item? kzbin.info/www/bejne/nKvJf2tqoKmknbc

[@giordanirivas294]

Hi Doug, I applied this solution to a sample I have. I have a Product table where the price is, a Customer table where I have many repetitive elements, and I have 6 tables for unique elements. Each of these tables with unique tables are connected to the Customer table (addressing the one to many relationships) and I also have a connection between the Product table and one of the unique tables (addressing the one to many relationship). I created a measure "xIRR" and the formula works. I created a pivot table showing the xIRR by the different elements but it only works for 2 out of the 6 tables with unique elements. Did I provided enough info to diagnosed why it is not working for the rest of the elements?

[@simonhill9090]

Looks good, could you also get a list of the products under each category in the resulting report?

[@DougHExcel]

Suppose so, depending on how the pivot is built out

[@lisbongraffiti242]

When you add this kind of tables for a many-many relationships it can be considered a star model or is a different kind?Thank you

[@fernando36881]

Hi, i follow the tutorial the calculation in potentialSales row is correct but the grand total didn't calculate true it's like show the maximum value, do you know what wrong?

[@MountainSnowFlyer]

This video was very helpful, and it does seem to be working for my case. I'm trying to organize my data from a main table using some groups that have a many to many relationship. However, I keep getting an error message that says: "A Pivot Table report cannot overlap another PivotTable report". Any idea what is causing that?

[@DougHExcel]

Hi MountainSnowFlyer, thanks for the comment. Basically the update to one of the pivot tables in a worksheet will extend the column/rows onto another pivot table on the sheet, so a solution is to move the pivot tables away from each other (i.e., to another range or another worksheet)

[@hamedu2965]

What about if you have a date column too?

[@noradorah8053]

Hello, can you have a "many-to-one" relationship in a power pivot excel file?

[@mattmatt245]

Can't you just aggregate "prodcat" table (group by category), then merge it with "cust" table on category field ?

[@cassandramah9786]

Great video! I'm working on a problem where I need to add another dimension to the measure formula to calculate a proportion. Essentially, CALCULATE( (sum of variable A in table 1) / (count of variable B in table 2). I get the error: "Calculation error in measure 'NameofMeasure': A function 'COUNT' has been used in a True/False expression that is used as a table filter expression. This is not allowed". Any suggestions? :)

[@DougHExcel]

Not sure...maybe wrap it in with a FILTER function? CALCULATE( FILTER ( (sum of variable A in table 1) / (count of variable B in table 2))

[@motionpicts]

Hi Doug, what if you don't have ProductID in your Product table, will this method be workable? thank you

[@DougHExcel]

Yes

[@themerchant7494]

Sir please help, how about many to many relationship using dates

[@marcodivito8527]

Great job! One question. Same example as yours, but a DATE instead of a PRICE (so no sum or counts in “Values”) - namely, a product order date for a specific customer. Would like to show all the orders to be placed in a date. Doing so, I would like to put the date at second column (after “Customer” but before “product” in your example). Unfortunately, it seems that the date is not linked to the product to be ordered (all the dates are present even though, from raw data, no orders should be present all the days). Thanks for your help! Marco

[@DougHExcel]

The concept would be similar but a calendar table will be the bridging table between the two.

[@marcodivito8527]

Doug H it seems doesn’t work unfortunately - I have created a relationship between order dates present in the table “prod” and a new table (“calendar table”). It continues showing all dates present in the calendar table (no sense) and then the product associated with the customer. Thanks for the help

[@French_Connection]

@@marcodivito8527 I also have similar situation. I was able to show number of dates within a month, quarter, etc. Totals were still wrong, but all within ranges was OK. But had to use blank so that it was not counted.. Could not take it one step further ... to map categories for groups.

[@errolm8313]

I'm still getting errors.... Will this concept work with setting up a relationship in access and then importing into power pivot?.... I'm now at 7 tables and still can't get referential integrity between them (so it can be a clean import into power pivot)

[@DougHExcel]

haven't tested this with Access....

[@LargeLondonGirl]

What happens when a new customer is added to the “cust” table...do you need to manually update the “uniquecust” table as well?

[@DougHExcel]

Yes you would need to manually update. The other option would be to create a query on the cust table to pull out the unique customer names and then output that as the uniquecust table and then use it as part of the bridging table...that might be another video :-)

[@good2see]

@@DougHExcel There must be a way to update the unique tables automatically.

[@john4thelove]

You can create a power query that saves the distinct results to a table :)

[@cavorka216]

When I do this Excel creates a Connection tied to the filename. If I create a copy the new file breaks when updating. Do you know how to solve this?

[@DougHExcel]

Sorry, don’t understand the comment/ question... maybe make a post to the mrexcel.com forum.

[@casst346]

what if you have many field columns with many to many between 2 or 3 tables

[@DougHExcel]

that would be additional bridge table, but it's not ideal

[@peterfj6218]

Jesus titty fucking christ. This is an absolute game changer. I've struggled with this for days and I finally managed to make it work. Thanks alot for the video!!

[@DougHExcel]

Oh my😆 Glad it helped!

[@msoffice6037]

Can you please tell what is the name of picture you used for this video (blonde girl pointing at table)

[@DougHExcel]

Purchased a licensed version for this pic and it's called "Database Table - technical concept, girl pointing screen"

[@msoffice6037]

@@DougHExcel Thank you very much for reply!

[@05khanha]

Sumif formula in column C would be quicker.. ;)

[@DougHExcel]

Hi RLprod, thanks for the comment!