cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jawed Regular Visitor
Regular Visitor

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

Accepted Solutions
SuperSayan Regular Visitor
Regular Visitor

Re: Return value from a different table

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

Re: Return value from a different table

You can try LOOKUPVALUE


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

SuperSayan Regular Visitor
Regular Visitor

Re: Return value from a different table

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"?

Jawed Regular Visitor
Regular Visitor

Re: Return value from a different table

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 Regular Visitor
Regular Visitor

Re: Return value from a different table

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

Re: Return value from a different table

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).

Jawed Regular Visitor
Regular Visitor

Re: Return value from a different table

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

Re: Return value from a different table

Yes you may try...

SuperSayan Regular Visitor
Regular Visitor

Re: Return value from a different table

@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

Jawed Regular Visitor
Regular Visitor

Re: Return value from a different table

Thank you @SuperSayan

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)