bridging table with unique entries to create relationship between 2 nonunique tables not working

[dave_blount]

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]

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

[dave_blount]

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. 

[dave_blount]

[BetterCallFrank]

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

should do the trick 🙂

HTH,

Frank

[dave_blount]

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.

[dave_blount]

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])

[geoffwood]

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?

[andre]

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

[dave_blount]

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