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

Get 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

Reply
TM_Evan
Helper I
Helper I

Unable to do table with 3 or more sources

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;

1 ACCEPTED 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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
TM_Evan
Helper I
Helper I

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?





Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

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. Smiley Wink





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.