cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jawed
Helper II
Helper II

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
nvpraveenyakkal
Resolver III
Resolver III

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 @nvpraveenyakkal. Are you suggesting one column for each table starting from Indexed type?

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

View solution in original post

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 IV
Super User IV

You can try LOOKUPVALUE


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors