0
particleandwave

Issue with Many to Many Relationships in Power BI Report Server - User can no longer open a file

Submitted by on ‎11-16-2023 07:58 AM

Users are able to select a "Many to Many" relationship from the Data Modeling Properties panel, even though Many to Many is not supported in Report Server and not available from the Edit Relationship box. If a user selects "Many to Many" from the Properties panel, saves and closes their file, they will not be able to open their file again.

 

Properties Panel where Many to Many is available for selection:

2023-11-16_10-46-05.png

Edit Relationship box where Many to Many is not available for selection:

2023-11-16_10-47-32.png

 

Error Message when the user attempts to open a file in which they've saved a Many to Many relationship:

document permissions error.PNG

 

 

Delivered v-xiaoyan-msft (Community Support) responded

Hi  @particleandwave ,

 

Many to Many relationships are not supported in Power BI Report Server and are not available for selection in the Edit Relationship box. This is why you are encountering errors when trying to open the file again.

To resolve this issue, I recommend the following steps:

1. Remove the Many to Many relationship from the Data Modeling Properties panel: - Open the Power BI file that is causing the issue. - Go to the Data Modeling Properties panel. - Locate the Many to Many relationship that was selected. - Remove the Many to Many relationship by selecting a different relationship type (e.g., One to Many or Many to One).

2. Save and close the file: - After removing the Many to Many relationship, save the changes to the file. - Close the file to ensure that the changes are applied.

The workaround is to create a bridge table with a list of the distinct values from one of your columns (you can do this with a calculated table using an expression like VALUES( table[column] ).then you create a many to 1 and 1 to many relationship chain.

 

 

Best regards.
Community Support Team_Caitlyn

 

 

 

 

 

See more ideas labeled with:
2 Comments (2 New)
Comments
v-xiaoyan-msft
Community Support
Community Support
‎11-17-2023 01:23 AM
Status changed to: Delivered

Hi  @particleandwave ,

 

Many to Many relationships are not supported in Power BI Report Server and are not available for selection in the Edit Relationship box. This is why you are encountering errors when trying to open the file again.

To resolve this issue, I recommend the following steps:

1. Remove the Many to Many relationship from the Data Modeling Properties panel: - Open the Power BI file that is causing the issue. - Go to the Data Modeling Properties panel. - Locate the Many to Many relationship that was selected. - Remove the Many to Many relationship by selecting a different relationship type (e.g., One to Many or Many to One).

2. Save and close the file: - After removing the Many to Many relationship, save the changes to the file. - Close the file to ensure that the changes are applied.

The workaround is to create a bridge table with a list of the distinct values from one of your columns (you can do this with a calculated table using an expression like VALUES( table[column] ).then you create a many to 1 and 1 to many relationship chain.

 

 

Best regards.
Community Support Team_Caitlyn

 

 

 

 

 

particleandwave
Frequent Visitor
‎11-17-2023 05:59 AM

The issue is that Many to Many is available for selection in the Data Modeling Properties panel when using the desktop version for Report Server. Why is this available for selection if it's not supported?

Idea Statuses