Prevent Power BI from chaging direction of a relationship

[danextian]

Hello,

I have an Invoice table which contains a column that holds the unique ID's to link to other tables (overpayment, prepayment and payment, creditnote). Each of the other table contains one or more rows of the same Unique ID and each Unique ID in a table cannot be found in all other tables.

I created four relationships with invoice table on the one side of thse relationships:

• invoice[unique id] to overpayment[unique id]
• invoice[unique id] to prepayment[unique id]
• invoice[unique id] to payment[unique id]
• invoice[unique id] to creditnote[unique id]

After applying/saving these relationships though, I wondered why I coundn't use all these columns in a table visual. It turns out that Power BI has reversed the relationships. Any idea how to prevent this?

[parry2k]

@danextian based on your input, you have invoice on one side of relationship and all other tables on many side, correct?

if that is the case, you drop any column from invoice table and you can drop columns from other table and it will work. Not sure what the actual problem is?

[danextian]

Hi @parry2k 

I have quite a lot of tables so it will be quite confusing to be using the relationship view so I instead tried adding the relationships using Manage relationships. The Invoice table was on the one-side prior to closing the dialogue box but was moved to many after closing it. The other tables are now on the one-side.

[parry2k]

@danextian That does't sounds right to me, I have no idea what your data looks like and I think that is the first thing to resolve

[danextian]

Yes. It is. I can't proceed unless it's been resolved. I wish I could post the pbix but the data is very confidential. I'm using the Nov 2019  version of Power BI.

[parry2k]

@danextian Can you try to change the relationship by clicking the relationship line? If it is putting invoice on many side, it means thats how your data is , not sure wht else to tell, you have take small sample of your dataset and try to isolate the problem by connecting with one table. There is no other way to resolve it.

[danextian]

Hi @parry2k ,

I realized my statement was incorrect. I set the direction to One-to-Many but after clicking OK on the dialogue box, the direction has changed to Many-to-One. Power BI did not change the sides but instead changed the direction so If I put invoice[Unique ID] and all other Unique ID columns in a table visual, Power BI returns an error saying that it can't determine the relationship between these columns.