Can't determine relationships between the fields.

Question

I have two datasheets, one containing annual costs by month and bank account, and the other one contains datailed informations about the cost on its&nbsp;respective bank account.&nbsp;AnnualCosts&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DetailedCostMonthNumber&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DescriptionBankAccount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BankAccountValue&nbsp;I can't create a relationship between them because it is a many to many relationship, so I created a bridge table, using all distinct values of AnnualCosts' BankAccount.AnnualCosts&nbsp; &nbsp; &nbsp; &nbsp; BankAccount&nbsp; &nbsp; &nbsp; DetailedCostMonthNumber&nbsp; &nbsp; &nbsp;BankAccount&nbsp; &nbsp; &nbsp; &nbsp; DescriptionBankAccount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BankAccountValue&nbsp;&nbsp;This is the relationship of the tables:&nbsp;I'm trying to create a some visuals, but I can't, because Power BI says that I can't&nbsp;Can't determine relationships between the fields.&nbsp;What am I doing wrong?&nbsp;&nbsp;&nbsp;

anonymous · Answer

HI&nbsp;Anonymous,
&nbsp;
I don't think you can direct use both side table columns to create visual. In my opinion, I'd like to suggest you use bridge table column instead the original relationship columns to link those fields from different tables.
&nbsp;
Regards,
Xiaoxin Sheng

anonymous · Answer

Anonymous, it worked. I created a new column named "AccountID" on my bridge table and merged that new column with the other two. But if I want to display the BankAccount of each table in the same visual, I can't show all as "Don't summarize", one on them has to be "Sum". Can you explain me what is happening? Why this error happens?

anonymous · Answer

HI&nbsp;Anonymous,
How did you create the visual, can you please share more detail?
&nbsp;
You can create table visual like below, bold part can store records with 'Don't summarize' mode:
Razao columns(mutiple), Conta column, Opex columns(mutiple).
&nbsp;
Regards,Xiaoxin Sheng

anonymous · Answer

What do you mean by a bridge column in this scenario? How would one derive that?

anonymous · Answer

Anonymous,&nbsp;I created a new column named ContaId on the bridge table.&nbsp;Example:&nbsp;Before:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Now:Table Conta&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table&nbsp;ContaColumn Conta&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Column Conta&nbsp; &nbsp; &nbsp; &nbsp;Column ContaId&nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp;&nbsp;Than I merged the both tables (Razao and Opex) with the table Conta to get the column ContaId and created a relationship between them using the new column.&nbsp;&nbsp;Hope it helps you.&nbsp;&nbsp;&nbsp;

Forum Discussion

Can't determine relationships between the fields.

5 Replies

Related content

Relationship

Can't determine relationships between the fields

Relationships

Relationship Issue

Creating relationships