topic Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server in Report Server

Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Anonymous — Fri, 26 Apr 2019 11:01:14 GMT

I have a PBIX whose data set has many to many relationship within the tables. I have created it in normal power BI desktop app. When I am trying to open the same PBIX on Power BI desktop for reporting server , I am getting an error : "This file uses many to many relationships or a composite model which combines DirectQuery sources and/or imported data. These models arent currently supported in Power BI Report Server". Please help.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

rwyoung01 — Thu, 13 Feb 2020 20:34:17 GMT

Does anyone have an answer for this. My report server is on Jan 2020 and still can't upload my pbix

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

mpsrshl — Thu, 13 Feb 2020 21:02:36 GMT

Create a table in between that holds single combination of the keys coming from the two tables. then join this new table against each outer table.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Anonymous — Tue, 14 Jul 2020 11:37:56 GMT

I'm too having the same issue. There are lots of tables involved. Someone, please help.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Anonymous — Tue, 14 Jul 2020 11:41:58 GMT

@mpsrshl can you please show us an example, it'll be really helpful.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

KaufmanNat — Fri, 04 Sep 2020 19:16:08 GMT

I am having this same issue. Has anyone identified a resolution? File cannot even be opened.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Jrussi1301 — Mon, 21 Sep 2020 20:34:55 GMT

Use the normal Power BI Desktop version, and upload the .PBIX file directly at the web portal of the Power BI server. Pay attention to the release, it must match your Power BI Server. For example, for Power BI Server january/2020, use Power BI Desktop december/2019 or below.

I think the deal with Power BI Desktop RS is that it does not support preview features. It seens to me that many to many cardinality on Power BI Server is a preview feature for now.

Please refer to:

https://github.com/MicrosoftDocs/powerbi-docs/issues/2294

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

atuljha — Wed, 28 Apr 2021 15:49:57 GMT

The problem i am facing is a dashboard was designed in an older version of PBI Desktop, and uploaded to the PBI servers. The Desktop and Servers were upgraded to a newer version.

now that i have to make an edit to the PBIX, i am getting this error. So whats weird is, the "older PBI Desktop" supported the feature, but the newer one does not.

Even if i consider rebuilding the dashboard (which was easy) the datamodel itself was complex, and had embedded SQL in it, which i dont have access to.

Anybody here was able to get access to the info from any other source (like with SSRS RDL, you could crack open the RDL in Notepad to get details)

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

robinwilson16 — Fri, 13 Aug 2021 21:45:31 GMT

Same issue for me in the May 2021 release.

How do we stop this happening. Surely it can't still be a preview feature 2 years later?

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Jrussi1301 — Sat, 14 Aug 2021 15:47:19 GMT

Solved: Bridge table - Microsoft Power BI Community

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Jrussi1301 — Sat, 14 Aug 2021 15:52:49 GMT

It is not a preview feature, since they do not intend do release it.

Solved: Re: Does Report Server support Many to Many relati... - Microsoft Power BI Community

Use the "table bridge" to solve this.

https://community.powerbi.com/t5/Desktop/Bridge-table/m-p/219805

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

robinwilson16 — Tue, 05 Oct 2021 22:57:30 GMT

Hello

Thanks for the reply but upgrading Power BI Server to the latest version initially works but then if the correct version of Power BI Desktop is used the report is migrated to the new version and initially works but after I close it I cannot edit it a second time and get the error but can open the original RDL and repeat the process with the same result.

We had to revert the Power BI Server back to the earlier version and are unable to upgrade it as with about 20 reports it would mean all of them would need to be recreated. The next problem is that Microsoft does not make the older versions of Power BI Desktop available and that had been upgraded so I had to find this from a 3rd party location (even Chocolately says it is installing the version I need but just goes and installs the latest one so must also have a bug!).

I got everything working again in the end but still upgrading Power BI Server to the latest version breaks all reports when everything is fine without applying upgades but means we cannot take advantage of any new features and need to ensure we keep the desktop installer somewhere safe.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

Jrussi1301 — Wed, 06 Oct 2021 17:50:05 GMT

I faced similar problem here. The only solution was to recreate all the PBIXs without many-to-many relationships. In order to do it, you use "bridge tables":

https://community.powerbi.com/t5/Desktop/Bridge-table/m-p/219805

After doing it, you can upgrade the server.

Re: Many to many cardinality causing issue to open PBIX in Power Bi reporting Server

riskybell — Tue, 04 Jul 2023 21:02:20 GMT

Same issue here with a bunch of reports. I'm paying $5k a month for my seat license and it doesn't support a simple join?