Forum Discussion

Jawed's avatar
Jawed
Helper III
8 years ago
Solved

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 "Actu...
Need Help
  • SuperSayan's avatar
    SuperSayan
    8 years ago

    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
SuperSayan's avatar
SuperSayan
Resolver I
8 years ago

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's avatar
Jawed
Helper III
8 years ago

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.

  • SuperSayan's avatar
    SuperSayan
    Resolver I
    8 years ago

    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
      • SuperSayan's avatar
        SuperSayan
        Resolver I
        8 years ago

        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.