Forum Discussion

Cipriano's avatar
Cipriano
Icon for Helper III rankHelper III
4 years ago
Solved

Get value string in related tables.

Hello everyone,

I need to obtain the column value of a related table at level 1, from another table at level 5. Using the RELATED function I can only get to the immediate level, which would be that of table 4.

searchString = RELATED(TableD[id]

 

Data example:

 

Table A    
Level-1IDName  
 A01Product 1  
 A02Product 2  
     
Table BIDNamefk_tableA 
Level-2B01SubProduct 1A01 
 B02SubProduct 2A01 
 B03SubProduct 1A02 
 B04SubProduct 2A02 
     
Table CIDNamefk_tableB 
Level-3C01Category1B03 
 C02Category2B03 
 C03Category1B04 
 C04Category2B04 
     
Table DIDNamefk_tableC 
Level-4D01SubCategory1C01 
 D02SubCategory2C01 
 D03SubCategory3C01 
 D04SubCategory1C02 
     
Table EIDNamefk_tableCCALCULATE
Level-5E01Sale1D01Product 2
 E02Sale2D02Product 2

 

Calculate = Value of column name of Table A, id A02.

Thank you very much!

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Cipriano,

     

    Try this calculated column in TableE:

     

    Table A Name = 
VAR vFK_D = TableE[fk_tableD]
VAR vFK_C =
    MAXX ( FILTER ( TableD, TableD[ID] = vFK_D ), TableD[fk_tableC] )
VAR vFK_B =
    MAXX ( FILTER ( TableC, TableC[ID] = vFK_C ), TableC[fk_tableB] )
VAR vFK_A =
    MAXX ( FILTER ( TableB, TableB[ID] = vFK_B ), TableB[fk_tableA] )
VAR vResult =
    MAXX ( FILTER ( TableA, TableA[ID] = vFK_A ), TableA[Name] )
RETURN
    vResult

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    Cipriano,

     

    Try this calculated column in TableE:

     

    Table A Name = 
VAR vFK_D = TableE[fk_tableD]
VAR vFK_C =
    MAXX ( FILTER ( TableD, TableD[ID] = vFK_D ), TableD[fk_tableC] )
VAR vFK_B =
    MAXX ( FILTER ( TableC, TableC[ID] = vFK_C ), TableC[fk_tableB] )
VAR vFK_A =
    MAXX ( FILTER ( TableB, TableB[ID] = vFK_B ), TableB[fk_tableA] )
VAR vResult =
    MAXX ( FILTER ( TableA, TableA[ID] = vFK_A ), TableA[Name] )
RETURN
    vResult