How to create many to many relationships

[bebeto]

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

Thanks!

[Bjoern · Accepted solution]

To be honest, I was referring to this article:

http://social.technet.microsoft.com/wiki/contents/articles/22202.a-practical-example-of-how-to-handl...

[Bjoern]

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.

[bebeto]

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 · Accepted solution]

To be honest, I was referring to this article:

http://social.technet.microsoft.com/wiki/contents/articles/22202.a-practical-example-of-how-to-handl...

[bebeto]

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 · Accepted solution]

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.

[mdrammeh]

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?

[megm001]

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

[hengsworld]

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? 

[bebeto]

Thanks greggyb!