Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Can't determine relationships between the fields.

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:

relationship.png

 

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?

 

 

 

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Anonymous
Not applicable

@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
Not applicable

@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?

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.