Reply
Member
Posts: 57
Registered: ‎10-05-2015
Accepted Solution

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!


Accepted Solutions
Established Member
Posts: 129
Registered: ‎07-14-2015

Re: How to create many to many relationships

Highlighted
New Contributor
Posts: 544
Registered: ‎10-27-2015

Re: How to create many to many relationships

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


All Replies
Established Member
Posts: 129
Registered: ‎07-14-2015

Re: How to create many to many relationships

[ Edited ]

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.

Member
Posts: 57
Registered: ‎10-05-2015

Re: How to create many to many relationships

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...

 

Established Member
Posts: 129
Registered: ‎07-14-2015

Re: How to create many to many relationships

Member
Posts: 57
Registered: ‎10-05-2015

Re: How to create many to many relationships

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!

Highlighted
New Contributor
Posts: 544
Registered: ‎10-27-2015

Re: How to create many to many relationships

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.

Member
Posts: 57
Registered: ‎10-05-2015

Re: How to create many to many relationships

Thanks greggyb!

Frequent Visitor
Posts: 6
Registered: ‎02-24-2017

Re: How to create many to many relationships

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? 

Member
Posts: 56
Registered: ‎07-30-2016

Re: How to create many to many relationships

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?

Regular Visitor
Posts: 20
Registered: ‎05-25-2017

Re: How to create many to many relationships

[ Edited ]

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