#41 Understanding relationships in Power BI Desktop
HTML-код
- Опубликовано: 27 июл 2024
- You normally build relationships in Power BI (connect two tables together) so you can work with the data in both tables as if they were a single table.
In this video you will learn how to create relationships, understand what and how to use the advance options and troubleshoot the most common problems.
Chapters:
00:00 Intro
01:20 Why you need relationships in power bi
02:44 How to create relationships
04:18 What is cardinality, direction and how to configure them
05:36 How to create relationships with multiple tables
07:30 What is Both/Single direction and how to use it
11:16 Troubleshooting relationships
Power BI file #41, get it in our Download Center -Community Downloads: 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:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
Detail, clear, informative, learnt alot from this video, thanks you so much for your sharing
And thanks to you for the feedback :)
/Ruth
@@CurbalEN Awesome video especially examples . Lucky to have you :)
Thanks to you for watching :)
/Ruth
Duplicating a table to resolve the inactive relationship...BRILLIANT!
I spent several sleepless nights because of this confusion which is clear now after watching this video; this is exactly what I was looking for, thank you so much Ruth
Good night then! ;) 😴
/Ruth
Probably this is the best video I have ever seen for the relationships in Power BI. This video tells me everything in detail with example. Thanks a lot
Thanks! :)
/Ruth
As always, something that I needed to better understand the finer points to how it impacts the reports/functions within PowerBi, you explain it with clear detail and examples to help ensure it makes sense. Thank you very much!
Just want to say Thank you so very much! I have spent hours trying to resolve an issue I had with Power BI - 4 minutes into your video and it’s solved! You are so ace!! Thank you for these amazing videos. 😀😀😀👍
All my questions about Relationships are now answered, I am so happy!. Thank you Ruth .
👏👏👏
That made me happy!
/Ruth
I'm so happy I found your site. Thank you for just being so good at what you do. Helping people learn in a simple way and making it fun to learn with you...
Thanks for the feedback and glad it was useful 😊
/Ruth
I love the way you say "not mine" relationships in the start XD
😂 didn't want to share you away!
It is a fantastic demonstration on Relationships in PowerBI. People with Cognos knowledge gets a dejavu feeling when they see duplication of tables to avoid circular loops :)
Great!! ;)
/Ruth
Great video! I enjoyed and learnt a lot! This will help me in working towards getting Power BI certification. Thank you Ruth.
So glad to have found your channel. Many thanks for clear explanations!
Thanks for the feedback!
/Ruth
Thanks a lot madam, You saved my day(night). If I could meet you, I will bow down to you. Thanks again, May god bless you..
Well done 👏👏
@@CurbalEN ☺️😊
Really helpful. Very clear and to the point illustration.
This was so informative.
Thank you!
Wow, that's a nice trick at the end. Thank you!
Yes. Learned a lot from this video. Thank you.
very well explained... will check out other videos too! thanks...
Thank you Ruth, God Bless you!
This is super awesome !!
Thank you so much
🎉🎉
Awesome! Very carefully explained, thank you.
You did an amazing job explaining. Thank you
Thanks for the feedback!
/Ruth
Very nice explanation. Thank you :)
That is what I was looking for, thank you!
Thanks for the feedback and glad it was helpful :)
/Ruth
great content !!
I love your videos the way you demonstrate , all your videos are very clear thank you so much
Thanks!
Thanks Ruth - i found this really helpful!
+Jonathan Wendon You welcome Jonathan! I am planing to do more videos on relationships later on :)
/Ruth
Great video. You are very informative. Thanks!
Thanks a lot for the great videos. your instructions are the only ones i understand! explained simply and understandably. Top
Thanks for the feedback!
After 3 years is still greatly useful! :)
Yes, the principles still stand!
/Ruth
Thank you, your video is very informative, it helps me a lot! thanks!
Thanks Ruth. It was so informative.
Excellent video, Ruth. Thanks for the same. Normally, handling inactive relationships becomes critical when there are multiple date columns like Shipping Date, Order Date, Invoice Date etc. In these cases, USERELATIONSHIP function comes very handy and I think it'd be awesome to have a video demonstration of the same or it can be an extension of this video also. I guess that would cover the cycle related to handling inactive relationships through DAX. By the way, multiple date columns are pretty common in normal business scenarios. (:
+Deepak Agrawal Thanks Deepak, great idea!
This video was just an introduction on the topic, but as you describe so well in your comment, a deep dive is needed on the topic to do more advanced analysis.
Added to my to-do-list, both for Dax Friday's and also for topic videos.
Many thanks for your comments and ideas :)
/Ruth
You are most welcome, Ruth. (:
I have a playlist on modeling, maybe it helps?
m.ruclips.net/p/PLDz00l_jz6zyfWLZIu8psvTESmKuVDUoG
/Ruth
Enjoy! 😊
/Ruth
As always, wonderful material. Muchas gracias!!
Un placer :)
Wow Bravo Bravo!!! thank you so much Great explanation
Thanks :)
/Ruth
Muchas Gracias por este vídeo, Ruth!!.. sigo pendiente de cada entrega...
+Norberto Vera Reatiga Muchas gracias a ti!
Mucho más fácil y divertido hacerlos sabiendo que son útiles :)
/ Ruth
Finally I understood 👍🏻👍🏻👍🏻🎉🎉🎉, muchas gracias 😉
+SmartBimson Thrilled that I could help you!! Have a great weekend!
/Ruth
Great presentation. You make it very easy to understand. One note about this post though I would like to make. At one point you begin to explain why designating every relationship as "both" is not correct, but then you digress into explaining active inactive relationship, and you never come back to why "both" is not the solution for all use cases.
But great video, liked it very much.
Check this video for that:
m.ruclips.net/video/IlxqDCIuarU/видео.html
Good night from Spain!, I discovered you a few days ago and I Like so much your channel (sorry, my English is horrible), I suscribe it, the videos are incredibles , Thanks for this!!!
+Paco Diaz Hola Paco, buenas noches! Saludos de una Asturiana en Suecia. Donde vives en España?
Muchas gracias por el comentario y por ver mis vídeos! Si quieres podemos hablar en español :)
Saludos, Ruth
Tenía el presentimiento que eras española pero no quería meter la pata en mi primer post (por la entonación, te entiendo en inglés muy bien), vivo en Málaga, por cierto hoy 20º y trabajo como economista en una constructora, donde estoy aplicando powerpivot y powerbi en los modelos que manejamos, sobretodo powerbi es una gran ventaja por lo dinámico y potente, es la herramienta del futuro, además el poder publicar los informes y cuadro de mandos es una pasada. Estoy suscrito a varios canales que tratan estos temas y de repente me aparecieron videos de tu canal, los he visto y me parecen claros, concisos y de gran utilidad, así que me he suscrito porque me son de gran ayuda.
Muchas gracias por tus publicaciones, seguro te comento algunas dudas por si me puedes ayudar
+Paco Diaz Buen presentimiento! :)
Yo me voy a casa esta semana para las navidades, a ver si el tiempo sigue así!
Genial que los vídeos te ayuden, es todo un placer saber que son útiles.
Power BI es toda una revolución sobre todo para usuarios como nosotros!
Muchos saludos de nuevo, Ruth
Wow fantastic explanation!
"But don't worry, we're not going to talk about mine"...
😂😂😂
/Ruth
thank you so much you've just saved me
+Brayan Hernandez Mora My pleasure Brayan :)
/Ruth
thank you very clear and simple
Excellent and thanks for the feedback!
/Ruth
Very clear! I hope you make more video's :)
I have a playlist with more videos:
ruclips.net/p/PLDz00l_jz6zyfWLZIu8psvTESmKuVDUoG
/Ruth
Como siempre de gran ayuda, muchas gracias!
+Jesús Reyes De nada Jesús! Me alegro que te haya ayudado :)
/Ruth
Good insight thanx :)
+Ashish Singh Thanks to you for watching!
/Ruth
the file #41 looks different from the one used in video. When I try to bring DimEmployee table from Queries, I do not see it in the file. It contains only ManufactTable, StockTable and ProductID. Could you advise why is that and if any workaround should be used?
Hi, how could you enlarge the fonts in a single visualization as you demonstrated in the video? I find it very useful but have not idea how can I enlarge or shrink the font size on the fly. Thanks.
Great video. Thank you
Glad you liked it :)
/Ruth
Hello, thank you for the video. I can't find the pbix file of that video, the one in your website is different (table employee is missing for example). Could you please provide the right pbix file? Thank you!
It helped me a lot! Subscribed, liked!
Welcome!!
The One to One relationship between Employee table and Country table should have been a Many to One. PBI chose One to One because they did not have multiple employees from the same country. But, they certainly could in the future.
That is correct :)
This was the first time I've understood this bi-directional thing.
Music to my ears 🎶
/Ruth
mee too!!
Thank you very much for this video. Another way to solve this could be to ensure that all the dimensions you need to filter keeps in columns in the dim tables? In this case, we could add a new column in the product table to lookup the category for each products from the fact table, that's right?
Best tutorial 👌🏻👌🏻🙏🏻
🥳🥳
@11:19 you mentioned, 'one might think that setting a cross relationship to both direction would resolve everything, but that's not the case', yet the relationship between all tables were directional.
As such, does that mean one must always use direction to avoid any errors (as long as we follow STAR Schema)?
Happy to subscribe :)
Welcome to the channel!
/Ruth
Great Video
Thanks :)
/Ruth
Hi Ruth, Thanks for the tutorials they were wonderful.
I have a question if you don't mind, I am trying to make a relationship between two tables
one of them have two fields as a primary key and power BI allow me only to make a relationship between single columns
How to solve that?
Hi! Here is one way:
m.ruclips.net/video/vAvQ8pCnWDk/видео.html
/Ruth
Hi Ruth Thank you for the good explanation. It was very clear. But just confused in the last with circular loop. After duplicating the Dim Geo table which is Dim Geo(2). The relationship formed between Dim Geo(2) and Dim Emploee is Employee ID and Country ID. How's that possible. Awaiting for your response. Thank you
Hi Ruth, another excellent video! Quick question, am I correct in thinking that the only reason you would not have a "Both" way relationship is when there is a circular reference? Other than that the best option is to have every relationship set to "Both".
Hi Darragh, Check this video Out:
m.ruclips.net/video/IlxqDCIuarU/видео.html
/Ruth
The file that you have uploaded in download center with #41 is not for this tutorial. can you please update it. Thanks a lot for great explanation.✔👍
It is nice video for understanding many to many relationships.
If we make product of rice with many materials like threads material, Plastic Bags material Whatever(BOM), than how can we relate these materials with employees .Please tell us.
Thanks.
Ruth, sube tus archivos que usas de ejemplo, para hacerlo e ir practicando al mismo tiempo, gracias saludos desde el fin del mundo Chile.
Is it possible that without remove special charactors match the value
Hi
I got an issue I have 4 excel files (a, b, c, d) ppl joining company at different times (a, b, c, d) that uploaded into PBI.
However I manage to merge Query on all of them.. But when I do calculated column, on two file for example b and c... It doesn't give me the correct answer... However I know that I used outer left join on column A to join the rest of files (b, c, d)... Basically all files have name.. Now I want to see the attrition between the files.
Hi Umar,
For personalized help, please contact the Power bi community and follow this steps:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
Hi... How is cardinality used by PowerBI? Is it used for displaying in report view? If so, please tell how it is used?
Tack!
Super tack ;)
Hi Curbal, The download file you provided is a power bi . .format. . that is i don't get the raw table to start fresh . .or follow along your video. . . just like you are doing in the video . your explanation is good but I am not able to follow along. .kindly if possible give use the raw tables. .
You can download the raw tables using dax studio:
m.ruclips.net/video/-Gfgnk6R0Qw/видео.html
Highly didactical and better than the official book 70 778
Glad you liked it!
/Ruth
Amazing
😃
Can we just delete the inactive relationship between employee table and Geo table? Because the setting is correct among other tables, it should be able to pull out the country information in this way. Please kindly advice?
And I've noticed STOCK to DIM Region is many to one relationship, but DIM REGION to Dim Geo is one to one. I think all the data in lookup tables is unique without duplicate, so what's the reason that relationships are set up differently in these few?
From where we can get data source?
hi I want to add a row in the table, i understand we can add coloum. Can we add Row in table? Thanks
Sure, there are a lot of ways to do that. Now, youtube comments is not the ideal place to answer that, would you mind posting your question in the power Bi community? Thanks ,
/Ruth
Hi. Can you explain relationships in football data please
Awesome tip..can I get the file..I tried downloading from cloud.. seems the file has been moved 😶
Hi Manu,
Here is the link:
gofile.me/2kEOD/qvszolRY1
/Ruth
You have a very relaxing voice, dont you wanna also record some fairy tales so I can play it to my children? :]]
Why not ? 😄😄
hai, could you explain kpi chart, concepts, and purpose
Sure, One question though: what do you mean with KPI chart?
/Ruth
how to make slicers coming from different table work on different table
Hi, how can I make a connection with a measure between two tables?
Fact table: date, product, name id 1, name id 2, name id 3
Dim table: name id, name
I want to pair the names with the products.
Can you help me?
Hi Ruth. Could you please help on interactive visualizations.
Hi!
Please check this video on how to get Power BI support:
m.ruclips.net/video/oXFOebuvRz0/видео.html
/Ruth
What if you only have one spreadsheet? Is the idea of relationships still relevant? Like are you supposed to break some columns apart from the main document to create a key to then create relationships with the rest of the spreadsheet?
Yes, maybe these videos will help you:
m.ruclips.net/video/V2mA40Z-R30/видео.html
And
m.ruclips.net/video/4vfAz7XDuH0/видео.html
/Ruth
@@CurbalEN Thanks, this was super helpful. So what if I've already made some graphs with my single spreadsheet of data? If I now go and divide it up into smaller tables and create relationships between them, will that affect the graphs I've already set up?
one thing, How you make text size bigger without using using format key??
I'm new in this BI tool.
Hi! Which text size ?
/Ruth
Curbal video 2:17 you select data. By default its size was small, you did something to get it bigger.
Oh got you! Go to the format pane ( paint brush icon to the right) and there you will be able to format the visual, including text size ;)
/Ruth
Hi Ruth I am looking for a video through which I can learn how to manage relationships between multiple fact and multiple dimension tables. I have found various posts where it has been suggested to create a link table, I would like to learn how to create a table between tables to manage relationships ,If you already have a video please direct me to it.
Thanks
Check this one:
m.ruclips.net/video/vAvQ8pCnWDk/видео.html
/Ruth
@@CurbalEN thanks a lot for your response now I have learnt to create a table between two tables, can you please let me know have you created any video where you have explained how to manage relationships Between multiple fact tables and multiple dimension tables or can provide any insight how can I do that.
Thanks
hello Ruth,
Could you explain how to achieve Many to many relations in power BI please......This will help a lot.
Best Regards\Pratik
There are many ways to do it and here is one :
m.ruclips.net/video/vAvQ8pCnWDk/видео.html
/Ruth
Thanks for the quick info.. 👍👍
great
🎉
/Ruth
🎉
/Ruth
why my Power Bi is not detected the relationship automatically. I have tick this option in the settings, what i am doing wrong. Please could you explain. Thank you very much.
Sometimes power bi can not detect them and you have to do it manually.
/Ruth
Can't you have loaded all the tables at the same time ?
Can do it in web version?
Don’t think so
create risk of circular relationships which is not allowed.
Apparently my relationship tab is not shown. Can you help me please !
That means that your power bi file is not importing data but using a direct query.
/Ruth
Thank you ❤️
You welcome!
/Ruth
Manage relationship on the right side is not shown. I don’t know what to do. I even tried reinstalling.
If the data is in Director Query mode the relationship tab is not available. Check that with the person that created the file.
/Ruth
1.How can I load multiple files with multiple sheet,column order can be different in different pages.2.how to load multiple file type as well as from table data into a single power bi dataset.3.i have 4 power bi desktop file which has same kind of data for 4 different periods (import mode). How to load into single pbix file from all 4 pbix file.4.how to load data from multiple power bi datasets to single power bi desktop .5.i have one complecated question ,which is unanswered in power bi community.can you pls tell me your username in community that I can tag you there.
I have a different problem related to BI permission, please get back to me
Hi Sam,
The best place to get support is the power bi community:)
/Ruth
0:09 buu :)
😂😂😂 Next time ;)
-Ruth
nice joke lol
What did I say? 😂😂😂
/Ruth
I can't follow this. super slow and confusing. The accent loses me. I wish you had someone I could understand when they speak.
Turn on captions! RUclips understands me quite well ;)
/Ruth
@@CurbalEN k thanks, good idea.
Have a glass of water so that your mouth isn't dry and we can't hear you swallow your saliva every 30 seconds its terrible and makes it difficult to watch the video
+Oladimeji Fashola Have a glass of wine and relax, it will make the video more enjoyable ;)
/Ruth
Curbal a glass of wine and data analysis doesn't go hand in hand Ruth
+Oladimeji Fashola Try it! It will surprise you!