Tricky calculated column lookup

Question

Hi All&nbsp;In the following example table I want to do a&nbsp; calculated column to look up from the application table to employer table returning the Parent Employer.name.&nbsp;I've filled in what the outcome should be in the applications table in red text.&nbsp;Thanks in advance.&nbsp;&nbsp;Applications table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Employer table&nbsp;&nbsp;EmployerIDEmployer nameParent Employer Name&nbsp;&nbsp;&nbsp;ParentIDEmployerIDEmployer name11Employer AA1Employer AA1&nbsp;&nbsp;&nbsp;1111Employer AA112Employer BB1Employer BB1&nbsp;&nbsp;&nbsp;1212Employer BB113Employer CC1Employer CC1&nbsp;&nbsp;&nbsp;1313Employer CC114Employer DD1Employer DD1&nbsp;&nbsp;&nbsp;1414Employer DD115Employer AA2Employer AA1&nbsp;&nbsp;&nbsp;1115Employer AA216Employer BB2Employer BB1&nbsp;&nbsp;&nbsp;1216Employer BB217Employer CC2Employer CC1&nbsp;&nbsp;&nbsp;1317Employer CC218Employer DD2Employer DD1&nbsp;&nbsp;&nbsp;1418Employer DD2

alexisolson · Accepted Answer

There are PATH functions that can handle recursive hierarchy structures like I think your Employer table is intended to have. See here for details on that:&nbsp;https://www.daxpatterns.com/parent-child-hierarchies/
&nbsp;
For this, you can probably get away with two lookups like this:
Parent Employer Name =
VAR _ParentID =
    LOOKUPVALUE (
        Employer[ParentID],
        Employer[EmployerID], Applications[EmployerID]
    )
VAR _ParentName =
    LOOKUPVALUE (
        Employer[Employer name],
        Employer[EmployerID], _ParentID
    )
RETURN
    _ParentName

Forum Discussion

Tricky calculated column lookup

2 Replies

Related content

Tricky calculation

TMDL - calculated column

Again a Tricky CALCULATE

Direct Lake Model - Calculated Column/Tables

Tricky - MAX Date Calculated Column

Applications table			Employer table
EmployerID	Employer name	Parent Employer Name	ParentID	EmployerID	Employer name
11	Employer AA1	Employer AA1	11	11	Employer AA1
12	Employer BB1	Employer BB1	12	12	Employer BB1
13	Employer CC1	Employer CC1	13	13	Employer CC1
14	Employer DD1	Employer DD1	14	14	Employer DD1
15	Employer AA2	Employer AA1	11	15	Employer AA2
16	Employer BB2	Employer BB1	12	16	Employer BB2
17	Employer CC2	Employer CC1	13	17	Employer CC2
18	Employer DD2	Employer DD1	14	18	Employer DD2