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:
Edit Relationship box where Many to Many is not available for selection:
Error Message when the user attempts to open a file in which they've saved a Many to Many relationship:
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.