Forum Discussion
Continued ContributorData shows in PBI Table View but I get error when adding field to table viz.
I have PBI Desktop version Feb 2025 on Windows Server 2019 v10.0, I'm getting data in Import mode from a MS SQL Server database.
Problem: I run a query using 2 tables with a left join in SSMS and it works fine. So I replace my PBI query call it "lineitem" with the new field "shipvia" from the other linked table "shippingmethods" and in PBI Table view all the data shows correctly.But when I try to drag the ShipVia field to my table viz I get an error: "can't determine relatioship between fields". But this ShipVia already works in PBI Table view.
Is this a bug? This is the only change I made.
I normally add each table separately and link them in "Model view" but I got an error doing that as well when I dragged ShipVia to my table viz. The error was the same as above.
So SSMS (SQL Server Management Studio) is linking the tables just fine but PBI is not.
Thank you.
It would take a few hours to make artificial data, and I got the report to work my remaking it from scratch. The original report was a spaghetti bowl of many-to-many relationships which was probably part of the problem.
This issue can be closed now.
7 Replies
croberts21We've had another bug (still ignored to this date, discovered and reported in 2023) with linking table where the key field is a single letter. In that but PBI forces the letters to all uppercase which breaks the linkage.
In this case above the linking fields are both integers (SHIP_MODE_ID), but they are displayed as left aligned in SSMS.
To remake this report from the ground up would take a lot of work and is not an option for us.
v-dineshyaCommunity Support
Hi croberts21 ,
Thank you for reaching out to the Microsoft Community Forum.
Please provide sample data of two tables , which your are trying to make relationship. Do not include sensitive information.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you- croberts21
It would take a few hours to make artificial data, and I got the report to work my remaking it from scratch. The original report was a spaghetti bowl of many-to-many relationships which was probably part of the problem.
This issue can be closed now.
- v-dineshya
Hi croberts21 ,
As you mentioned, Many-to-Many relationships was probably part of the problem. to fix the Many-to-Many relationships.
Please refer below official documents and community forum threads.
Many-to-many relationship guidance - Power BI | Microsoft Learn
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
Solved: Hi everyone, Could someone explain how to avoid ma... - Microsoft Fabric Community
Solved: Many- Many Relationship - Microsoft Fabric Community
Solved: How to Manage Many-to-Many Relationships in Power ... - Microsoft Fabric Community
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you- v-dineshya
Hi croberts21 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.Regards,
Dinesh