Is MANY TO MANY, too many? | Power BI many to many relationships explained
HTML-код
- Опубликовано: 9 июл 2024
- In this video we are going to go through Power BI Many to many relationships that were realeased in the POwer BI desktop July 2018 update.
This is what we will cover:
1. What are Many to many relationships
2. Why you should avoid them
3. How to avoid them
Questions, comments? Let me know in the comment box!
Links to related videos:
Composite models: • COMPOSITE MODELS EXPLA...
About Power bI relationships:
Many2many: • Building relationships...
Relationships: • #41 Understanding rela...
Wrong totals:
• DAX Fridays! #25: Wron...
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Linkedin: goo.gl/3VW6Ky
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#SUBSCRIBE #CURBAL
#POWERWEEK #CURBAL #POWERBI #POWERBIUPDATE #MVP
You know, you are really smart. I appreciate your instructor style. Not that you are trying to avoid M-to-N relationship, but more to understand the way of today's columnar horizontal BI tools behave with data. The essence is most of us are two-dimensional thinkers trying to make sense of cube-dimensional thinking. We can understand 2x2 = 4, but when it comes to understanding 2 to 8th power, we run into possible 256 combinations our mind cannot right away visualize. In database modeling, what you've explained is resolution via Ternary key associating two M-to-N tables, where it is impossible to apply a binary key to the many-to-many relationship tables. Again, thank you for your help. Stay safe. :)
And thanks for your explanation and feedback 😊
I have seen multiple many to many relationship videos on RUclips but this was the best one. Thanks and Keep up the good work Ruth 👍
Thank you!
Thanks, Ruth. This was very informative and I love the way you explained it. Have a great day and please stay safe and healthy.❤
Great video as always. Easy to understand. Thank you very much Ruth!
Yey!! 😃
/Ruth
Thanks so much for Ruth, you are so kind to share your insights and experience with us. As always, I found it super useful. Please keep up the good work!
Yey!! 🎉🎉
/Ruth
Brilliant! Great Job. This solved a puzzle struggling with for a long time.
Music to my ears!!! 😃
Gran explicación, muchas gracias. Tus videos han sido de gran ayuda para aprender Power Bi, Dax y el modelado de datos.
Un placer!
/Ruth
This is a GEM 💎!! Thank you!
Wonderful! And thanks for the feedback ;)
/Ruth
I just find your youtube videos. I think you´re great. Awesome explanations. I've subscribed your channel.
Keep going.
congratulations!
Welcome!! ;)
Love your channel and advice on power BI!
Thanks Indre!
Much appreciate this!
Ruth you’re a life saver.
Thank you!
Very good thank you
Great vid!! Thanks for sharing your skills :)
And thanks to you for watching!! :)
/Ruth
this solved the issue that I was experiencing. Thanks so much!!!!!!!!!!!!!!
Delighted to hear that!
Great Video. Thanks
Thank you very much
Thank you very much.
Thank you Ruth, great video
Phew! Glad it was useful ! :)
/Ruth
I had many relationships many times. My girlfriend didn't like it. Now I just have one relationship with her. Stay away from many-to-many relationships like the plague. Good advice.
😂😂😂 true! Like life itself !
/Ruth
I really impressive. I like the way that you usted to explanó the things. You clarified me how relationships work. Thanks you. I subscribed to to your Channel
Welcome to the channel y Saludos! :)
/Ruth
Happy Christmas and thanks again
Merry Xmas!!
/Ruth
Great video..helped me to resolve the issue. Thanks for sharing 😊
My pleasure!
Thank you sooooooo much! I'm used to Informatica, and it is really hard to get used to PowerBI.
Push through! The rewards are behind the corner ;)
/Ruth
Thanks Ruth
Thank you Ruth👍
My pleasure!
I LOVE YOU!!!!
Thanks
Hi Ruth, if I have same values in multiple columns how do I make a relationship? Table has Employee number, Equip1, Equip2, Equip3, Equip4 upto 10 items and their model, Serial etc. Equip1 have computer, Laptop, Monitor, Keyboard etc. and same data in Equip 2, 3, 4. So if I want to know how many laptops, desktops etc for each employee, how do I make relationship?
Great video! It will be nice to hear also, how to prepare formulas for BASKET analysis. If you need to understand how many and wich products were bought, when one specific purchased.
Oh yes! I did that for a customer a while ago, I can prepare a video in the future.
/Ruth
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
Ruth podrías hacer un día de estos , un vídeo sobre el proyecto que mas te ha gustado de Power Bi que hayas efectuado y explicando detalles hechos y retos que tuviste en el camino desde el punto de vista objetivos y desafíos Dax.
Los proyectos en los que trabajo son confidenciales, tendría que preguntar a mis clientes.
Has visto los vídeos de la FIFA?
Los hice en vivo y en directo casi...
/Ruth
Thank you for the explanation! Would like to see a measure multiplying two columns from each table in examples with bridge tables, for instance at 11:13, Amount and StockNo. Is this possible?
thanks
Hi - I know this is only partly related, but have you found a way of managing the tables in the relationship view so that they stay where you put them ? I have re-arranged my tables sooo many times now
You mean the relationships or the actual tables?
/Ruth
FINISHED WATCHING
👏
Sometimes I have a lot of columns that would require over 20 bridge tables for example. I’ve gotten around it by making a concatenated column to make it unique 1 to 1. Just wondering if you recommend any other methods? Thank you. Your videos are always educational.
Creating unique keys is a great way to get around the problem. Depending on the model, I would probably do the same :)
/Ruth
But why it should consider ie. product filter when being connected by date ? What do You mean by ignoring? If it was "normal" relation it would be the same. You do not use any filter on dates (in both tables at the same time tables as there is no date table which connects both)
Exact! Ruth. The best advise is: DO NOT USE M2M RELATIONSHIPS and use traditional method.
I tried (as you) when it was released and immediately forgot it.
Really good and practise video!
Josep.
Thanks!! Those that are more familiar with M2M can probably use it , but for the rest of us mortals... best to stay away!
/Ruth
Why the relationship direction is single for calendar and product at the end not both? Or it doesn't matter.
Oh it will cause a cycle!
can i ask which whiteboard tool you are using? great video by the way i am always a fan of a bridge table
Hi!
Epic Pen, great tool and Thanks!
/Ruth
Thanks a ton
Hi in your solution, did you put the table like in 6:31" ? Because I can't really see how that will be solved
Not sure what you mean with "put the table"
/Ruth
Great Ruth, but what if there are products in the budget table and not in sales. Now the product filter does not work. So filtertable product must have values from both tables, without duplicates
I didn’t mean that this is the master way to solve this, I just pointed it as one solution, hope that was clear!
That is the trickiest thing about DAX, no formula or model fits all!
The point of the video is explaining the many to many relationships:)
/Ruth
Curbal youre right, but I am always analysing the problems
No worries, you are right too ;)
/Ruth
Impressive ✋🏻
Yey! 🎉
/Ruth
Great vid! Tks so much for sharing this. However after registered for member, I was trying to join the community and download sources but I couldn't. How can I contact for help please? Much appreciated! 🙂
Send me an email here:
Curbal.com/contact
And I will get back to you tomorrow.
/Ruth
Everything is awesome...but please change the image after 0:55 for subscription call...it is not good..heheehhe. You are looking good in that image..it is lifeless...But definitely I got the point you explained in this video.
Hahahaha it is meant to be “bad” , but I didn’t know it was that disturbing! I will change it as soon as I can!
/Ruth
Frankly speaking, it reminds me a cancer patient who has just returned from hospital after a chemotherapy treatment..loosing all the hair over head...sorry....pleeeease change it immediately....
😂😂 is not that bad!!
/Ruth
the food example seems like a one:one relationship with composite key is date+food_name. Feel free to correct me if I'm wrong.
There are duplicates in both "Date" & "Product name" in both tables, so how come it's a one:one relationship?
Most of the time I hear many to many is dangerous because of potential ambiguity in the model. Could you elaborate on different scenarios when they really make sense? Thank you. Great video
Sqlbi has a few, check them out if this one didn’t help.
/Ruth
@@CurbalEN Thanks Ruth, yes I already checked them but still looking for more. It's a quite interesting topic. Best regards
I agree! As I stay away from M2M I don’t have good cases to showcase. Have you tried asking in the power bi community?
/Ruth
@@CurbalEN good idea. Just prefer video over description. ;) Visual learner
So the main point can be boiled down to: many to many = bad; and bridge table = good
Oh my Lord! ...and it took me 17 min to say that...You are hired!
/Ruth
I have actually worked in Sweden today, but only in Lund.
Oh! Next time take the train 🚊 up ;)
Would be great to meet you !
/Ruth
Really impressive.Can we have relationship between tables using multiple columns
No, check this video for how to do that:
m.ruclips.net/video/aVcHCfOAtZA/видео.html
EPIC PEN!
Yes! It is great !! :)
Thanks for this. You are really cute :)
Qlik Sense allows users to create many to many, with no warning !!!
I am not a qlik user, so I cant tell if that is a good or bad thing...
Do you get unexpected results while using many to many?
/Ruth
I love the way you explain , but in this video the image is not good :(. So I was not able to follow you
All my videos are HD, check the video quality!
Suck why Explain unrelated place alot?
No idea why you tried to say, but I have feeling it wasn't a positive comment.
Happy Friday either way!
Thank you very much