Can't determine relationships between the fields.

[Anonymous]

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 respective bank account.

AnnualCosts                      DetailedCost

MonthNumber                   Description

BankAccount                      BankAccount

Value

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        BankAccount      DetailedCost

MonthNumber     BankAccount        Description

BankAccount                                     BankAccount

Value

This is the relationship of the tables:

I'm trying to create a some visuals, but I can't, because Power BI says that I can't Can't determine relationships between the fields.

What am I doing wrong?

[v-shex-msft]

HI @Anonymous,

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.

Regards,

Xiaoxin Sheng

[Anonymous]

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

[Anonymous]

@Anonymous,

I created a new column named ContaId on the bridge table.

Example:

Before:                                                                   Now:

Table Conta                                                          Table Conta

Column Conta                                                     Column Conta       Column ContaId  

1                                                                                     1                                   1

2                                                                                     2                                   2

3                                                                                     3                                   3

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. 

Hope it helps you.

[Anonymous]

@v-shex-msft, 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?

[v-shex-msft]

HI @Anonymous,

How did you create the visual, can you please share more detail?

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

Regards,
Xiaoxin Sheng