Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
5 years ago
Solved

Lookup

 

i have 2 tables. one is Active HC and other is Inactive HC.

In Inactive HC i don't have L2 name. but it is available on Active HC table. i need to find it based on the below logic.

 

Logic- From inactive table we have to take the Empid and go to active Active table. And apply 2 filter. 

Fiter 1 =first we have to get the previous month of the report date which is mentioned in Inactive ( in our case previous months based on Inactive is Jan - 1 - 2021.)

Filter 2 = In Active table filter for "Opening"

so based on the above table i will go to Inactive table and take the EMPID 122. and the Report date is Feb - 1- 2021 ( In Inactive Table). now i have to go to Active table and first filter is previous month. so i will select Jan-1-2021( Active Table). and Filter 2 is "Opening" ( Active Table). and it will give me L2 as Jack.

 

How to get this working

 

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    unnijoy 

    pls try this

    L2 = 
MAXX(FILTER(ALL(Active),Active[Report Name]=EDATE(Inactive[Report Name],-1)&&Active[OP/CL]="Opening"&&Active[EMPID]=Inactive[EMPID]),Active[L2])

7 Replies