Forum Discussion
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,
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
11 Replies
You can try LOOKUPVALUE
Thank you Greg_Deckler. I will try Lookupvalue and report back...
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
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?
