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
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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