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
SBR1D's avatar
SBR1D
Icon for Helper III rankHelper III
2 years ago

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.