Forum Discussion
bridging table with unique entries to create relationship between 2 nonunique tables not working
Hi, I'll be very specific here as it may be some minutae of what I'm doing that is preventing this from working.
I have two tables :- Bugs and Results. Both contain a FailureCategoryID column, but there are non unique entries in the columns in both tables. My goal is to produce a table visual that contains the Results entriers along with a BugID entry from the Bugs table that correspons to the FailurecategoryID for that entry in the results table. The Bugs to FailureCategoryID is a 1:N relationship.
So I created a bridging table in my dataset which contains all the unique values of FailureCategoryID from the Results table (Bugs table would have many many more).
The use of a bridging table has been suggested elsewhere in this support forum.
When creating the BridgingTable<->Bugs relationship, I entered the bridgingTable.FailurecategoryID first and the Bugs.FailurecategoryId second and listed it as a 1:N relationship. Ditto for the bridgingTable <->Results table.
The relationship seems to have been created OK, and is visualized as such in the relationship visual.
Then when I go to my reports tab and create a Table visual, I can add columns from bridgingTable & Bugs, or bridgingtable & Results, but never both bugs and results. It always errors out with a very non informative error of "cannot display the data because powerBI cant determine the relationship between two or more fields."
Any ideas what I'm doing incorrectly? Thanks, Dave
9 Replies
Hi, I'll be very specific here as it may be some minutae of what I'm doing that is preventing this from working.
I have two tables :- Bugs and Results. Both contain a FailureCategoryID column, but there are non unique entries in the columns in both tables. My goal is to produce a table visual that contains the Results entriers along with a BugID entry from the Bugs table that correspons to the FailurecategoryID for that entry in the results table. The Bugs to FailureCategoryID is a 1:N relationship.
So I created a bridging table in my dataset which contains all the unique values of FailureCategoryID from the Results table (Bugs table would have many many more).
The use of a bridging table has been suggested elsewhere in this support forum.
When creating the BridgingTable<->Bugs relationship, I entered the bridgingTable.FailurecategoryID first and the Bugs.FailurecategoryId second and listed it as a 1:N relationship. Ditto for the bridgingTable <->Results table.
The relationship seems to have been created OK, and is visualized as such in the relationship visual.
Then when I go to my reports tab and create a Table visual, I can add columns from bridgingTable & Bugs, or bridgingtable & Results, but never both bugs and results. It always errors out with a very non informative error of "cannot display the data because powerBI cant determine the relationship between two or more fields."
Any ideas what I'm doing incorrectly? Thanks, Dave
- BetterCallFrank
Resolver IV
Hi Dave,
can you please post a screenshot of your data model?
And also the DAX you used to create the bridging table
helps us helping you :-)
Frank
Thanks Frank. To create the contents of the bridge table, I did the following:-
- In Query Editor right-click on the table FailuresFromKusto and column failureBucketID (I need unique values for this column as that is the one I want the relationship to the BugDataFromOasys built on).
- Add the bottom of the menu select ‘Add As New Query. This will create a new list with all the values from that column
- On the newly created list right-click and select ‘Remove Duplicates’
- When that is complete right-click on the list and select ‘To Table’. Rename column and table to FailurebucketID and FailureBucketId_kusto.
- Close & Apply your changes
- Create the new relationships. They create OK as per the diagrams below, but then the problem as outlined in the original post exists.
