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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors