Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

0

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

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

 

 

Status: 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

 

 

 

 

 

Comments
v-xiaoyan-msft
Community Support
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

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?