Create Relationship Between Three Tables

[FancyOgre]

Hello Everyone !

I am trying to create a relationship between my tables in power query. I have three tables. The first table has name and address field.

 

And The Second Table has Name, Address, Income Amount and Date on which the income was received.

 

The Third Table contains Name, Address, Expense and the date on which the expense happened.

 

Now I'm trying to create Relationships between these table so that i can create a report where it show the name of user and the income he/she has received and how much he has spent as expense. However, since the income and expense table contains duplicate values of Name and Address, i am unable to create a relationship between Income and Expense table. I am able to create Relationships between Table 1( with name and address) and income table and also between Table 1 and Expense Table but i have not been able to create Relationship between income and expense table to achieve my desired result. Is there any way forward to this ?

[rubayatyasmin · Accepted solution]

Hi, @FancyOgre 

you can create relationship like this, less workaround. (might be error-prone)

sample value

power bi actually auto created the relation. i tried to visualize 

it is giving the correct value.

if you do not want to do this way then you might consider creating a composite key in both table. then create a relationship using that key. and best practice? create a date table and add a relationship with the date column. 

[FancyOgre]

I'm getting this error: The relationship you're creating lets you filter Income by Table1. but Excel 16.0 allows only one filtering path between tables in a Data Model. Deactivate existing relationships between the tables or change their filter direction. This relationship can be as inactive.

[Mkarwa-123]

@FancyOgre you dont need a relationship between income and expense table. Create a relationship of firsttable with income and expense separately also create a date table and create a relationship with income and expense with date table.