Get value string in related tables.

[Cipriano]

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-1	ID	Name
	A01	Product 1
	A02	Product 2
Table B	ID	Name	fk_tableA
Level-2	B01	SubProduct 1	A01
	B02	SubProduct 2	A01
	B03	SubProduct 1	A02
	B04	SubProduct 2	A02
Table C	ID	Name	fk_tableB
Level-3	C01	Category1	B03
	C02	Category2	B03
	C03	Category1	B04
	C04	Category2	B04
Table D	ID	Name	fk_tableC
Level-4	D01	SubCategory1	C01
	D02	SubCategory2	C01
	D03	SubCategory3	C01
	D04	SubCategory1	C02
Table E	ID	Name	fk_tableC	CALCULATE
Level-5	E01	Sale1	D01	Product 2
	E02	Sale2	D02	Product 2

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

Thank you very much!

[DataInsights · Accepted solution]

@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

[Cipriano]

Thank you very much!