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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Jawed
Helper III
Helper III

Return value from a different table

Hi everyone,

 

Am running into difficulties when I try to dice information. Below is the Relationship between my tables. I want to create a column where it looks at the Subaccount Name in the "Actuals" and returns it "Indexed Type". Currently, when I try to use RELATED, it can go as far as the "Subaccount Name" table. Is there any other method?

 

Thanks,

 

Screenshot.png

1 ACCEPTED SOLUTION

@Jawed If you have only 1 "indexed Type" by "Subaccount name" I believe it would make more sense to keep have an "Indexed Type" column in your "Subaccount name" table.
Usually those "link" table are created in power query by removing duplicates in another table and if this is the case, you can just keep the "Indexed Type" column.

 

However if for some reason that doesn't fit what you want to do, you can access the value with the below DAX formula after selection "Add New Column" in your "Actuals" table:

 

Indexed Type= 
var Suba=RELATED('Subaccount Name'[Subaccount Name])
var indexed=FIRSTNONBLANK(SELECTCOLUMNS(FILTER('CoA,'CoA'[Subaccount Name]=indexed),"Index",'Subaccount Name'[Subaccount Name]),"Index")
return indexed

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

RELATED is used in Many side of a Relationship. In your case it fails because RELATED could not access a single row while going from Subaccount Name to CoA (1 to many relationship where you are supposed to use RELATEDTABLE).

 

Try to pull the column from table to table starting from Indexed Type to CoA (use RELATED) and CoA to SubaccountName (use RELATEDTABLE) and SubaccountName to Actuals (use RELATED).

Thank you @Anonymous. Are you suggesting one column for each table starting from Indexed type?

Anonymous
Not applicable

Yes you may try...

SuperSayan
Resolver I
Resolver I

Hi @Jawed

What type of data is you "indexed Type" (text, number...)?

For 1 subaccount name you have only one indexed type or you need to recover another data somewher to select the proper "Indexed Type"?

Hi @SuperSayan. The names are just fancy ways of describing my Chart of Account hierarchy. So, the Indexed Type tell me if something is an Asset, Liability, Income, Expense, etc. Index Class then tells me if an Asset is fixed or long-term, etc. 

 

So, to answer your question, the Indexed type is a text and each subaccount has got only one Indexed type. However, one Indexed type is assigned to many different subaccounts through the hierarchy which I just explained. Hope it makes sense.

@Jawed If you have only 1 "indexed Type" by "Subaccount name" I believe it would make more sense to keep have an "Indexed Type" column in your "Subaccount name" table.
Usually those "link" table are created in power query by removing duplicates in another table and if this is the case, you can just keep the "Indexed Type" column.

 

However if for some reason that doesn't fit what you want to do, you can access the value with the below DAX formula after selection "Add New Column" in your "Actuals" table:

 

Indexed Type= 
var Suba=RELATED('Subaccount Name'[Subaccount Name])
var indexed=FIRSTNONBLANK(SELECTCOLUMNS(FILTER('CoA,'CoA'[Subaccount Name]=indexed),"Index",'Subaccount Name'[Subaccount Name]),"Index")
return indexed

Thank you @SuperSayan

@Jawed you're welcome.

If the answer is OK for you, please accept as solution so that the topic can be marked as solved and may be used by other members if they need a similar answer.

Hi @SuperSayan. I am still trying to find a solution. The main reason for breaking the Chart of Account was to avoid having so many columns in the data. Whilst the solution suggested is one way of doing it, it does however, negate the purpose of having a leaner tables in the first place. I havent given up yet though and once again, thank you so much for helping me out.

Greg_Deckler
Super User
Super User

You can try LOOKUPVALUE



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you @Greg_Deckler. I will try Lookupvalue and report back...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.