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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MTOnet
Helper III
Helper III

Cannot Determine Relationship When Trying To Display Information From Two Tables

I am trying to create a visual that displays information from two tables, but when doing so, I am getting an error stating the relationship cannot be determined.  This is probably due to how my data is, as the two tables that have information I am trying to display are linked by an intermediary table.  There can be elements that are in both tables, but not in the intermediary table.  I figure this is what is generatingthe error and that I need to create a dynamic Table to display this information, but I haven't been able to come up with an formula that gives the result I am trying to achieve.

 Example File 

My Data

Table 1Table 2
ID         1:*  Source
Table 2:Table3
Target  *:*  ID2

Table 1 contains all objects within the collection

Table 2 contains how all these items ar linked, like Parent:Child

Table 3 contains a set of properties related to specific actions that can be done against a specific type of item in Table 1

Result I am Looking for

The ability to display all records in table 1 and if there is a relationship between items in Table 1 and found in Table 2 as the Source and Target, then display the iformation from Table 3.  Table 3 may have multiple entires that relate to Table 1, as there could be mulitple options assigned in Table 3, so multiple rows for the Table 1 value shold be displayed for each option.
Expected results for ID's 480834, 480839 & 345278 (345278 is not to be displayed, since there is no link between Table 1 & 3 via Table 2)

result.png 

I've tried creating the relationships between the tables, including a bridge table for the Targt ID from Table 2 to eliminate the *:* relationship between the two table.  However, I think this is what generated the error, as it is finding the same value in the two tables, but there is no link between them via Table 2. 

1 ACCEPTED SOLUTION
BI_Jo
Resolver III
Resolver III

Hi 

 

I had  look at your example pbix file and was a bit confused by what table 4 was.  So I ignored that and set up the following relationships.

Table 1 'ID' to Table 2 'Source'
Table 2 'Target' to Table 3 'ID2'

BI_Jo_0-1603511556391.png

 

Then I created a table visual with the columns from table 1 and table 3 and the data appeared as you wanted it (although id 480834 had more than one matched row in table 3)

If you want to display the values from table 1 that don't have any realtionship with values in table 3 in the visual you need to set the following in the visulization panel for one of the columns from table 3 i.e status:

 So on the status field, cick on the drop down arrow and tick on 'show items with no data'

BI_Jo_1-1603512354859.png

 

Hopefully I've understood your question and it helps in some way!

Jo

View solution in original post

2 REPLIES 2
BI_Jo
Resolver III
Resolver III

Hi 

 

I had  look at your example pbix file and was a bit confused by what table 4 was.  So I ignored that and set up the following relationships.

Table 1 'ID' to Table 2 'Source'
Table 2 'Target' to Table 3 'ID2'

BI_Jo_0-1603511556391.png

 

Then I created a table visual with the columns from table 1 and table 3 and the data appeared as you wanted it (although id 480834 had more than one matched row in table 3)

If you want to display the values from table 1 that don't have any realtionship with values in table 3 in the visual you need to set the following in the visulization panel for one of the columns from table 3 i.e status:

 So on the status field, cick on the drop down arrow and tick on 'show items with no data'

BI_Jo_1-1603512354859.png

 

Hopefully I've understood your question and it helps in some way!

Jo

AllisonKennedy
Super User
Super User

I can't get your file to open, but I suspect cross-filter direction may have something to do with your problems.
https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.html

Can you copy some sample data tables from Excel that show the correct cardinality for your relationships?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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