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
bebeto
Helper III
Helper III

How to create many to many relationships

Is it possible to create many to many relationships? Or do I create it trough an intermediate table like in Analysis Services?

 

Thanks!

2 ACCEPTED SOLUTIONS
Bjoern
Continued Contributor
Continued Contributor
greggyb
Resident Rockstar
Resident Rockstar

You should not need to write any special DAX to get the N:N relationship working in Power BI Desktop. You just need to create bi-directional relationships to the bridge table. The bridge is necessary, but as long as you have all bi-directional relationships in the chain of relationships between two tables, all filter context will flow appropriately between them.

View solution in original post

9 REPLIES 9
Bjoern
Continued Contributor
Continued Contributor

For in-file models it works (includes imports to SQL)

For AS Tabular 2014 it does not work.

For AS MD I do not know (they work out of the box with the MD server, but I do not know how PBI handles it)

For AS Tabular 2016 it should work.

Nowadays I am interested in Multidimensional and in-file models (I understand that you refer this as create your own model in Power BI Desktop from importing differente files, is it?)

 

In this case, in-file models, I can not view the option Many to Many in the option "Cardinality" in a relationship options...

 

Bjoern
Continued Contributor
Continued Contributor

Ok. So, as I can see, it has to be modeled with an intermediate table (like in SSAS), as then use DAX functions to get the correct functionality.

 

Thanks!

greggyb
Resident Rockstar
Resident Rockstar

You should not need to write any special DAX to get the N:N relationship working in Power BI Desktop. You just need to create bi-directional relationships to the bridge table. The bridge is necessary, but as long as you have all bi-directional relationships in the chain of relationships between two tables, all filter context will flow appropriately between them.

Could you show as a demo of a bi-directional relationships? I am new with Power BI and would like to understand a little bit more about how you would go about creating such a relationship?

@greggybAn example with images would be really helpful. Thanks!

Hi @greggyb, When I tried to make a bi directional relationship it is giving me a circular dependency detection! I think this is because the table contain calculated columns.. What can I fix this problem? 

Thanks greggyb!

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.