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

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.