Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |