Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ironryan77
Advocate II
Advocate II

How to select related tables?

Does this functionality work?  I have tried using Select Related Tables when getting data from SQL Server and OLE DB, but each time I select this option, it says:

We didn't find any more related tables.

Am I doing something wrong?

 

1 ACCEPTED SOLUTION

Yes, you have to create the relationships in the Power BI model even if corresponding relationships already exist in the source database. Power BI can automatically create relationships when you load two tables with matching key columns in a 1:M pattern, but I don't like to rely on that. Just manually set them up in the relationship manager.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
v-yuezhe-msft
Employee
Employee

Hi @ironryan77,

The “Select Related Tables” option is useful when users want to import multiple tables when connecting to a database. Without this option, users have to manually select all tables that they wanted to import, which often required an understanding of the underlying database schema so they would pick all tables that are related.

With this option, Power BI Desktop will automatically select all tables that have a direct relationship to one or more of the already selected tables.

Reference:
https://powerbi.microsoft.com/fr-fr/blog/44-new-features-in-the-power-bi-desktop-september-update/

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you, I understand this.  But how exactly does Power BI identify Related Tables since in my attempt so far it has not recognized any related tables, regardless of which I base table I choose?  I looked at your included URL and there is no details for how Power BI chooses these tables.

Hi @ironryan77,

As stated in that blog, Power BI Desktop will automatically select all tables that have a direct relationship to one or more of the already selected tables.

In your scenario, tables should have been established relationships in the SQL Server database or Oracle database before you import data from them to Power BI, this way, once you select one table and then click "Select Related tables" option, Power BI Desktop will select all tables that have a direct relationship to the selected table.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That's the part I'm not sure I understand.  Initially I thought that any tables which are sourced into stored procs on the same server, would become related.  But are you saying that you have to manually create table relationships from the Database Diagrams folder?  Is this the only way to create relationships?

db diagram error.jpg

Yes, you have to create the relationships in the Power BI model even if corresponding relationships already exist in the source database. Power BI can automatically create relationships when you load two tables with matching key columns in a 1:M pattern, but I don't like to rely on that. Just manually set them up in the relationship manager.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

I dont understand how this feature is supposed to work. Let say I am starting a new BI project, I have all my data in SQL Server, with all the tables nicely organized with PK and FK.

 

If I select my fact table and click on the select related tables button, it does not find any. The answer above says that the relationship must already exist in PowerBI, but I am starting my project, getting the data is the first think I am suppoesd to do. Am I missing something?

Yes RelatedTable function works for me.

Obivously, you need a relationship between your 2 tables.

RelatedTable goes from the One side to the Many side.

 

What is not working for you ?

Oh, so it sounds like I need to define relationships before I can select related tables?  I don't have any relationships defined in Power BI yet for these tables.  But many other tables are sourced from this one SQL table that I chose "Select Related Tables" on.  If I need to define relationships first, then what is the benefit of this feature?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.