Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
What do you mean by a bridge column in this scenario? How would one derive that?
@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.
@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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |