Forum Discussion
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...
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.
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.
Thank you for looking into this!