ฝัง
- เผยแพร่เมื่อ 2 ก.ค. 2024
- 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...
~-~~-~~~-~~-~
I couldn't agree more with other positive comments below. This is by far the best explanation video. Thank you so much!!
Man, you are the best
really
i spent so much time to figure it out
and you here just in 10 min explain everything ...
Thanks OV MOV, glad it helped!
I am going to give this a shot, it's been the best detailed explanation I found so far!
Many...many thanks....to you and the man behind your source- blog!!!
Much appreciated!
An EXCELLENT tuturial - clear, organized, professional - AND - an effective learning tool...cannot ask for more than that !!! Ha !!!
Wow, thanks!
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.
Thanks Derek Garner, glad it helped!
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!
Thanks soloseven007, glad it helped!
I read that blog months ago and could not make it work, thank you this! It WORKS!
Thanks john4thelove, glad it helped!
Excellent Way of Teaching... Thank You
Thanks and welcome!
Thank you very much!! very clear explanation!
You are welcome!
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.
Glad it was helpful!
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
Helped me very much, ty
You’re welcome!
thank you so much, it's so helpful and thanks again
It helps so much - Gazak Allah Khier - Allah will reward you
The calculation step solved my problem. Thank you so much!
Hi Antonio Matheus Tinoco, you're welcome!
Thanks in a million. Awesome!
You’re welcome!
Beautiful video. Thanks for the share.
Hi Syed Muzammil Mahasan Shahi, thanks for the comment!
This helped a lot to resolve a problem I have had for many months now!! Thanks!
Glad it helped!
OMG! You've just solved my problem. Love from India ❤️🙏👍
Glad it helped!
Thanks Dough H your video ended my 1 week search on the exact same issue I was having
Glad it helped!
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!
Glad it was helpful!
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!
OMG Awesome man.. you made my day ..! thanks a lot!
You’re welcome!
Thank you!
You're welcome!
Thanks a lot!!!
You're welcome!
Thanks man
You're welcome!
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.
thank you so much
You’re welcome!
Great job. Alas, my head is spinning lol
Hi Steve Sullivan, thanks for the comment!
hahaha.. Same here
Me too
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?
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?
Why doesn't support Power Pivot this out of the box like Microsoft Power BI does? Thanks for the really helpful video!
Merci
De rien!
thank you so much for these videos!
How to solve one to one relationship model in powerpivot, please?
Sorry, don’t understand the comment/ question... but it sounds more like a lookup item? th-cam.com/video/fzfN84kDqgU/w-d-xo.html
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?
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
Looks good, could you also get a list of the products under each category in the resulting report?
Suppose so, depending on how the pivot is built out
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?
What about if you have a date column too?
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?
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)
Hi Doug, what if you don't have ProductID in your Product table, will this method be workable? thank you
Yes
Hello, can you have a "many-to-one" relationship in a power pivot excel file?
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? :)
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))
Can't you just aggregate "prodcat" table (group by category), then merge it with "cust" table on category field ?
Sir please help, how about many to many relationship using dates
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?
Sorry, don’t understand the comment/ question... maybe make a post to the mrexcel.com forum.
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
The concept would be similar but a calendar table will be the bridging table between the two.
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
@@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.
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)
haven't tested this with Access....
what if you have many field columns with many to many between 2 or 3 tables
that would be additional bridge table, but it's not ideal
What happens when a new customer is added to the “cust” table...do you need to manually update the “uniquecust” table as well?
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 :-)
@@DougHExcel There must be a way to update the unique tables automatically.
You can create a power query that saves the distinct results to a table :)
Can you please tell what is the name of picture you used for this video (blonde girl pointing at table)
Purchased a licensed version for this pic and it's called "Database Table - technical concept, girl pointing screen"
@@DougHExcel Thank you very much for reply!
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!!
Oh my😆 Glad it helped!
Sumif formula in column C would be quicker.. ;)
Hi RLprod, thanks for the comment!