Forum Discussion
Issue with bridging table
Hi all,
I have stumbled upon some issue with a bridging table. It is big file that I work with and I can unfortunately not share them / screenshots but I will try to be as clear as possible here.
I have two queries I'd like to combine (excel files):
Query A (multiple excel files on sharepoint combined to one query)
| Order number | Country |
| 1 | US |
| 2 | Mexico |
| 3 | Spain |
| 4 | Mexico |
| 5 | Spain |
Query B (multiple excel files on sharepoint combined to one query)
| Order number | Value |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
| 5 | 40 |
| 6 | 50 |
| 7 | 60 |
| 8 | 70 |
My bridging table below is using the append function to append "Order number" column from both Query A and Query B. After appending I am also removing duplicates. I have already removed blanks from Query A and Query B.
Bridging table (appended):
| Order number |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
So all good...
Then I have linked my bridge table to with a many-to-one relationship from the Bridge table (order number) to Query A (order number) and from Bridge table (order number) to Query B (order number) - both with single filter.
So far all good..
When I try to create a table in the report view, I add the Order Number [Briding table]. If I then add Country from Query A or Value from Query B, then all works fine. But the issue occurs when I add both Country from Query A and Value from Query B since I will get an error saying "PowerBI cannot determine the relationship between two values".
The formatting of "Order number" is text in both queries and in the appended one as well. I have no idea why this is not working. Order number is always the 'lowest level' of detail my table should display.
What I'd like to have is as follows:
| Order number | Country | Value |
| 1 | US | |
| 2 | Mexico | 10 |
| 3 | Spain | 20 |
| 4 | Mexico | 30 |
| 5 | Spain | 40 |
| 6 | 50 | |
| 7 | 60 | |
| 8 | 70 |
Any help or thoughts would be greately appreciated! Thanks in advance.
8 Replies
Anonymous ,
Why do you need a bridge table here ? Doesn't merge both tables work ?
Hi camargos88 , I guess I could, but the tables are very big and have between 20-50 columns each, so I'd like to keep them separate if possible.
Anonymous
Set the filtering from QueryA to the bridge table to be bi-directional in the edit relationship window.
Hi jdbuchanan71 , if I do this, what is the impact? Will something be filtered out?
Hi jdbuchanan71 . I tried this and it does work fine when I am counting "order numbers". But when I am choose to not count "order numbers" (just dislpay them), I get the same errors. So it seems like somehow the values (country) could in theory be different for the same order number. But that should not be an issue - should it?
Edit: What I mean is that I can in theory have both "Mexico" and "US" as country value in two different lines from the same "order number" in Query A. But if I only have one "value" (line) for the same order number in Query B - will I then get an issue? That PowerBI does not know how to distribute the "value" from Query B into two different rows ("US" and "Mexico")?
Anonymous , if table one has only unique value for order no, then you use that as a master.
With the bridge table, only one table can be un summarized, the other one needs to have aggregated columns
