Where is many to many?

[MikeSeim]

I am trying to implement dynamic dimensions in PowerBI.  I am following along the guide provided here:  http://leanx.eu/tutorials/dynamic-dimensions-in-power-bi

All is well except the bridge table the guide requires seems a terribly hard requirement for my situation.  My thought was that perhaps the new Many-to-Many (M:M, N:N, *-*, *:*) cardinality would conceivably solve for this.  I would think the M:M relationship would prevent the need for a cartesian product bridge table bloating my workbook and my work in general.

So that was the goal.

As I endeavoured to test the theory I was disappointed to find that M:M wasn't even an option for me.  My only options included 1:1, 1:M, and M:1.  My data model consists of three data sources; all three are "SQL Server > Import > Advanced > Custom SQL Statement" based.

So what gives?  Why don't I get M:M as a cardinality option?  Can anyone help?

My PowerBI Desktop version

Version: 2.69.5467.1801 64-bit (May, 2019)

[v-shex-msft · Accepted solution]

HI @MikeSeim ,

I think you are work with power bi desktop report server optimization version, right? If this is a case, current some preview/new features only existed on power bi desktop side and not release to report server optimization version.

Install Power BI Desktop optimized for Power BI Report Server 

In addition, you can also take a look at the following document to know the limitations of M2M relationships:

Relationships with a many-many cardinality in Power BI Desktop#limitations-and-considerations 
Regards,

Xiaoxin Sheng

[MikeSeim]

Thank you!  That helps.  That must be it.  But to be clear, how do I determine if I'm on the "PowerBI Desktop Report Server Optimazation Version" or the "PowerBI Desktop Version"?

Also, is there perhaps some reference that could inform me and my team what features would not be available to us, assuming we're using the "PowerBI Desktop Report Server Optimazation Version"?

Finally, do you know of any plans to add M:M to "PowerBI Desktop Report Server Optimazation Version" and also to "PowerBI Report Server"?

[d_gosbell]

---

@MikeSeim wrote:

So what gives?  Why don't I get M:M as a cardinality option?  Can anyone help?

Why are you using the May 2019 version of desktop? Is this because you are deploying to a Power BI Report Server instead of to powerbi.com? If this is that case then that is your issue. Power BI Report Server does not support many to many relationships yet, that is currently only supported in the cloud environment (even with the latest Sep 2019 release)

[MikeSeim]

Thanks for responding.  I appreciate it.

Admittedly I'm with a large company and I don't really get to elect which software versions I'm allowed to us.  They've gotta go through all the corporate processes before we can get the latest and greatest.

But yet, we're publishing to "PowerBI Report Server".  It would appear that is the underlying issue.

What a bummer!

But hey... I'll throw the challenge out there for anyone who wants to take the bait.  I wonder if folks can test the concept of dynamic dimensions WITHOUT a catesian-product-bridge-table using many-to-many relationships and dummy-join-fields to make the connection.  To my mind, it seems like it would work in theory.

[d_gosbell]

---

@MikeSeim wrote:

But hey... I'll throw the challenge out there for anyone who wants to take the bait.  I wonder if folks can test the concept of dynamic dimensions WITHOUT a catesian-product-bridge-table using many-to-many relationships and dummy-join-fields to make the connection.  To my mind, it seems like it would work in theory.

---

I don't believe this is possible with m:m relationships, but I also think that the link you provided is not a great implementation. It will explode out the "bridge" table to many times the size of your fact table. I would suggest using a technique like the following where the filter table is only a union of the different dimension values that you want to dynamically swap. https://www.kasperonbi.com/dynamically-switching-axis-on-visuals-with-power-bi/