Forum Discussion
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.
My Data
| Table 1 | Table 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)
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.
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'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'Hopefully I've understood your question and it helps in some way!
Jo
2 Replies
- 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? 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'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'Hopefully I've understood your question and it helps in some way!
Jo
