Forum Discussion

Honne2021's avatar
Honne2021
Helper II
4 years ago
Solved

Index Match through DAX or Power Query

Hi,  I have tried doing this in excel but I need to use an array formula which eats up so much of my memory and my laptop eventually cant manage it. 😞 I hope anyone can assist me on this.  1...
General Comment
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Honne2021 ,

     

    Add a Level column first:

    Level = IF(CONTAINSSTRING([Team Name],"."),2,1)

    Then try:

     

    Result is Main Team = 
 var _table= FILTER(ADDCOLUMNS(CROSSJOIN('Table1','Table2'),"a",CONTAINSSTRING([Organizational Hierarchy:],[Manager])),[a]=TRUE())
 return MAXX(FILTER(_table,[Organizational Hierarchy:]=EARLIER('Table1'[Organizational Hierarchy:]) && [Level]=1 && [Team Name]<>"Team A"),[Team Name])


    Result is Sub Team = 
 var _table= FILTER(ADDCOLUMNS(CROSSJOIN('Table1','Table2'),"a",CONTAINSSTRING([Organizational Hierarchy:],[Manager])),[a]=TRUE())
 return MAXX(FILTER(_table,[Organizational Hierarchy:]=EARLIER('Table1'[Organizational Hierarchy:]) && [Level]=2 && [Team Name]<>"Team A"),[Team Name])

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

I'd strongly recommend splitting the hierarchy into multiple rows before trying to work with it further.

 

Why is the result Team B rather than Team A? You only want to consider the two levels above?

  • Honne2021's avatar
    Honne2021
    Helper II
    4 years ago

    Thank you for looking into this. The result is Team B because Team A is assumed to be the leadership team's group, the c-level employees like the CFO, CEO and the likes..I only need to get the employee's main team and their sub team. I did not break up the org hierarchy and just used the Lookupvalue formula in dax but it did not work.