Issue with bridging table

[Anonymous]

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.

[amitchandak]

@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

[Anonymous]

Can you elaborate on what you mean here? Not sure I follow. Both Query A and Query B contains "order number" values where some are unique and some appear in both queries. I want the bridge table to be the "master" since it contains all the unique "order numbers" from Query A and Query B.

[jdbuchanan71]

@Anonymous 

Set the filtering from QueryA to the bridge table to be bi-directional in the edit relationship window.

[Anonymous]

Hi @jdbuchanan71 , if I do this, what is the impact? Will something be filtered out? 

[Anonymous]

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")?

[jdbuchanan71]

Can you add the country to the other order table instead?

[camargos88]

@Anonymous ,

Why do you need a bridge table here ? Doesn't merge both tables work ?

[Anonymous]

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.