Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am attempting to create a table joining data from more than 3 tables in a single database.
In my example I am able to insert an Order Number (Order), Settlement Data (Order), and Full Name (Person).
When attempting to Insert the address (Property) I receive an error stating that it is unable to determine relationship between tables. Now if I take that field and give it its own window, I works fine. If I go to the problem table and remove name and re-insert address it works. If I then attempt to re-add name it gives the same error. This leads me to believe this is Power BI unable to do more than 2 sources?!?! Please let me know what I may be missing. I am very new at this so I am most likely missing something simple.
Thanks
Below from error:
Feedback Type:
Frown (Error)
Timestamp:
2017-04-18T15:04:18.6076305Z
Local Time:
2017-04-18T11:04:18.6076305-04:00
Product Version:
2.43.4647.541 (PBIDesktop) (x64)
Release:
February 2017
IE Version:
11.0.9600.18639
OS Version:
Microsoft Windows NT 6.3.9600.0 (x64 en-US)
CLR Version:
4.6.1 or later [Release Number = 394271]
Workbook Package Info:
1* - en-US, fastCombine: Disabled, runBackgroundAnalysis: True.
Peak Working Set:
705 MB
Private Memory:
650 MB
Peak Virtual Memory:
35.8 GB
Error Message:
Can't display the data because Power BI can't determine the relationship between two or more fields.
User ID:
28073380-b7db-4f13-bc95-79604c490bf0
Session ID:
ffb8463a-e1f9-4047-b790-fc8baab6bf87
Telemetry Enabled:
True
Model Default Mode:
DirectQuery
Snapshot Trace Logs:
C:\Users\egross\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot730206825
Performance Trace Logs:
C:\Users\egross\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Disabled Preview Features:
PBI_RedShift
PBI_Impala
PBI_Snowflake
PBI_shapeMapVisualEnabled
PBI_allowBiDiCrossFilterInDirectQuery
PBI_clusteringEnabled
PBI_esriEnabled
Disabled DirectQuery Options:
DirectQuery_Unrestricted
Cloud:
GlobalCloud
More details:
Not showing data for DataShape 'DS0' because it's not clear how these fields are related.
Activity ID:
ffb8463a-e1f9-4047-b790-fc8baab6bf87
Time:
Tue Apr 18 2017 11:02:52 GMT-0400 (Eastern Daylight Time)
Version:
2.43.4647.541 (PBIDesktop)
OData Error Message:
Not showing data for DataShape 'DS0' because it's not clear how these fields are related.
DPI Scale:
100%
Supported Services:
Power BI
Formulas:
section Section1;
shared #"pfm Order" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_Order = Source{[Schema="pfm",Item="Order"]}[Data]
in
pfm_Order;
shared #"pfm BuyerData" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_BuyerData = Source{[Schema="pfm",Item="BuyerData"]}[Data]
in
pfm_BuyerData;
shared #"pfm Contact" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_Contact = Source{[Schema="pfm",Item="Contact"]}[Data]
in
pfm_Contact;
shared #"pfm Person" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_Person = Source{[Schema="pfm",Item="Person"]}[Data]
in
pfm_Person;
shared #"pfm Property" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_Property = Source{[Schema="pfm",Item="Property"]}[Data]
in
pfm_Property;
shared #"pfm Title" = let
Source = Sql.Database("SQLDW", "SelectDb"),
pfm_Title = Source{[Schema="pfm",Item="Title"]}[Data]
in
pfm_Title;
Solved! Go to Solution.
What relationships, exactly, are established? Between which columns in which tables? What are their crossfiltering directions? Cardinality? How are you trying to display these? A matrix? If so have you put anything in the Values box?
Proud to be a Super User!
Another thing to add. I made sure relationships are indeed established. Why is Power BI having issues with a simple function which SQL queries can do all day long?
What relationships, exactly, are established? Between which columns in which tables? What are their crossfiltering directions? Cardinality? How are you trying to display these? A matrix? If so have you put anything in the Values box?
Proud to be a Super User!
Thank you for the reply. I have established relationships of all tables to the RootID. (The common denominator of all the tables).
Playing around with the crossfiltering did not make a difference. I am attempting to display these fields in a Matrixed table.
I also do not understand what cardinality is.
Upon your suggestion I did move some of the fields to the Values box and that DID have a positive result. Could you possibly please explain the difference between Values and Column/Rows?
Cardinality is many to one, one to many, one to one, etc.
Playing around with crossfiltering would only make a difference if you had something in Values and things still were not working correctly.
The matrix visual is specifically meant to show values for different combinations of rows and columns. Whatever you put in Values will be aggregated according to the intersections of those rows and columns. Measures often take advantage of the USERELATIONSHIP or CROSSFILTER formulas to manipulate how those relationships between tables work, and even if not there may be multiple relationships in play, so some kind of Value contents are needed to make the matrix function.
Proud to be a Super User!
I noticed that Power BI (so far) does a good job in selecting the cardinality correctly then.
As for Matrix vs Table, I totally see that now. I really appreciate your help as well as others in the community.
Yeah unless you're deliberately doing something weird it's pretty good at auto-selecting the right cardinality. But I never know if the person asking the question has done something weird.
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |