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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Andyr
Frequent Visitor

How to show the data in a many to many with bridge table

Hi, 

 

I've got two tables that are related with a bridge table in the middle to create a many to many relationship. 

The data is coming from different data sources. 

As an example:

Table Foo (From sql server)
   Id
   Name

Table Bar (From excell)
   Id
   Name

 
Table FooToBar (From another excell linking many Foos to many Bars)
   Id
   FooId
   BarId

 

I've set these tables up with relationships.


All I want to currently do is show in a table visual all rows in the Foo table and all rows in Bar table as they are realted by the bridge table as you would in SQL with inner joins relating the data simular to :

select * from @fooToBar ftb
inner join @foo f on f.id = ftb.fooID
inner join @bar b on b.Id = ftb.BarId

I've tried just using the table visual but as soon as I select columns from the 'bar' table I get an error unless I set the column to an agregate such as count.

Is this actually possible in PBI? 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yes,

There is an "old way" and the "new way" to handle this.

Please refer to the following article (you can jump directly to the "new way")

https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

 

Michael

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Yes,

There is an "old way" and the "new way" to handle this.

Please refer to the following article (you can jump directly to the "new way")

https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

 

Michael

Thanks Michael

The problem was that there was a duplicate Id in one of the tables, once I removed that the solution you pointed me to works a treat. 

 

Cheers

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors