Tricky calculated column lookup

[SBR1D]

Hi All

In the following example table I want to do a  calculated column to look up from the application table to employer table returning the Parent Employer.

name.

I've filled in what the outcome should be in the applications table in red text.

Thanks in advance.

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

[SBR1D]

Thank you so much. I will take a look into the link you have provided, I am not sure why the company chose to model the data like this as it is confusing. Unfortuanately, we use 3rd party software/databae so i am unable to change the model.

But your solution works so thanks and have a great day.

[AlexisOlson · Accepted solution]

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: https://www.daxpatterns.com/parent-child-hierarchies/

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