Power BI Relationship Help

[olindeman]

I have 2 excel files. One has all my main data but the data source does not always have a clean Account Name value, and many of my Accounts Number change from year to year. I have another table with Account Names all clean and the Parent Account Numbers. I need Power BI to link the 2 sheets. So for Example my full data set might have Account number 148 and 124 as Adventure Sports and Do Not Use Adventure Sport, the second sheet has 148 and 124 both as Adventure Sports with Parent account 148. So i want to have as a result in my slicer when i add 148 as the parent account it pulls the data from Full Data  on 148 and 124.

[olindeman]

I have 2 excel files. One has all my main data but the data source does not always have a clean Account Name value, and many of my Accounts Number chnage from year to year. I have another table with Account Names all clean and the Parent Account Numbers. I need Power BI to link the 2 sheets. So for Example my full data set might have Account number 148 and 124 as Adventure Sports and Do Not Use Adventure Sport, the second sheet has 148 and 124 both as Adventure Sports with Parent account 148. The result i need in my slicer is that when account number 148 is selected it pulls the data for both 148 and 124.

[v-yuezhe-msft]

Hi @olindeman,

Could you please share sample data of your scenario and post the expected result here?

Thanks,
Lydia Zhang

[olindeman]

Main sheet looks like this, this sheet has 30k plus lines a day

Account number	Account Name	Sale
148	Aventure Sport	3
124	DO NOT USE Adventure Sport	4
34567	Dons Cars	5

Second sheet looks like this has 160 accounts max

Account number	Parent Account	Account Name
148	148	Aventure Sport
124	148	Aventure Sport
34567		Dons Cars

My goal would be to relate the sheets somehow so that sheet 1 when it sees 124 it knows to attribute those sales to 148, however when nothing is in sheet two it uses sheet ones account number. Like with Dons Cars, it does not have a parent account so i need it to use 34567.

[olindeman]

Would this not be possible?

[Seth_C_Bauer · Accepted solution]

@olindeman You could create a calculated column in the 2nd table to determine the "Account" that you want to use...

Account = IF(Table2[Parent Account] = BLANK(), Table2[Account Number], Table2[Parent Account])