Forum Discussion

SBR1D's avatar
SBR1D
Icon for Helper III rankHelper III
2 years ago
Solved

Tricky calculated column lookup

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 o...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
2 years ago

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