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

      • Jawed's avatar
        Jawed
        Helper III
        8 years ago

        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.