Lookup with where clause

[Anonymous]

I have a table that looks as follows:

ID        Name           Mgr Name       level          Parent ID

56        Sh                    Ja                     1                      43

139      Du                   Ja                      1                      43

220      Re                    Ja                     2                       43  

486      Ca                   Ja                      3                       24 

43        Ja                     Fe                    1                       23

55        Re                    Sh                     1                      56

486       Ca                  Me                    1                       24

24         Me                .........

- Name is the employee name who is associated with manager. 

Ex: sh is an employee whose direct manager is Ja. Hence level = 1. Similar is the case for Emp Name Du

Re on the other hand is an emp who reports to, say Sh (Emp in 1st row). And Sh reports to Ja. Hence Re's level to Ja  =2

Parent ID is a calculated column based on lookup ffunction:

Parent ID = lookupvalue (Table[id], Emp Name, Mgr Name)

But as you can see in  the above example, all parent ID's get 43 (43 = ID of Ja; Last purple highlighted row). But I need Parent ID to show the ID of the manager whose Level =1

For Ex: When Re has Mgr = Ja and Level = 2; my parent ID formula pops up Ja's ID as Parent ID. But what I need it to show is ID of Re's manager when Level =1 (In this case 56; Last line in data table -> Re reports to Sh directly and hence level =1. Sh's emp ID is 56 hence she gets parent ID = 56. )

That is, For row 3 in the dataset, even if Re reports to Ja as Level 3, the parent ID for this row should be 56 (Where 56 is ID of Sh who is the Level 1 manager for Re)

I need to modify my formula for Parent ID to reflect this. 

Ideal output:

ID        Name           Mgr Name       level          Parent ID

56        Sh                    Ja                     1                      43

139      Du                   Ja                      1                      43

220      Re                    Ja                     2                       43  

486      Ca                   Ja                      3                       24 

43        Ja                     Fe                    1                       23

55        Re                    Sh                     1                      56

486       Ca                  Me                    1                       24

24         Me                .........

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could use this formula to get your requirement；

Parent ID = var _minlevel=CALCULATE(MIN('Table'[Level]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]))) return
var _directmgrname=CALCULATE(MAX('Table'[Mgr Name]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Level]=_minlevel)) return
LOOKUPVALUE('Table'[ID],'Table'[Name],_directmgrname)

Result:

Regards,

Lin

[kentyler]

Power BI data cannot represent hierarchies directly. DAX has a couple of built in functions to address this problem. You should probably try to look at them and then use them to address this problem rather than trying to roll your own. Check out this post https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax

"DAX provides five functions to help users manage data that is presented as a parent-child hierarchy in their models. With this functions a user can obtain the entire lineage of parents a row has, how many levels has the lineage to the top parent, who is the parent n-levels above the current row, who is the n-descendant from the top of the current row hierarchy and is certain parent a parent in the current row hierarchy?"