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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dave_blount
Regular Visitor

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 9
BetterCallFrank
Resolver IV
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:-

  1. 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).
  2. Add the bottom of the menu select ‘Add As New Query. This will create a new list with all the values from that column
  3. On the newly created list right-click and select ‘Remove Duplicates’
  4. When that is complete right-click on the list and select ‘To Table’. Rename  column and table to FailurebucketID and FailureBucketId_kusto.
  5. Close & Apply your changes
  6. Create the new relationships. They create OK as per the diagrams below, but then the problem as outlined in the original post exists.
  7. bridgeTable

powerBIBridge.png

@dave_blount double click each relationship and try to set its direction to "both"

 

should do the trick 🙂

 

HTH,

Frank

Thanks Frank, but that has the same error (I'd tried this already ) also the order of which table was on which side of the relationship.

Also, just to add, I did also try creatingthe bridging table via DAX (see below) and creating the relatonships then, but problem is the same.

FailureBucketUnique = DISTINCT(FailuresFromKusto[FailureBucketId])

I'm new here, so forgive me if I'm off base, but it sounds like the data structure is correct, it's the table that you're trying to produce that is causing the issues. The Bugs table entry can map back to a unique entry in the Bridging Table, but when you try to pull in fields from the Results table, Power BI has no way to know which entry to pull in as there could be multiples. 

 

I'm struggling to visualize the table you're trying to create, what data are you trying to pull in from the reults table for each bug?

I am guessing that you have blanks in your bridge table. 

dave_blount
Regular Visitor

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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