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.   Logi...
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])
amitchandak's avatar
amitchandak
Super User
5 years ago

unnijoy ,

Create a new table from Table one if it has all emp

 

summarize(table, Table[empid], table[L2])

 

Join it both the tbale on employee id

unnijoy's avatar
unnijoy
Post Prodigy
5 years ago

hi amitchandak ,

 

Thanks for your reply.

 

I need to create a calulated column for L2. like a lookup based on filter. Can you please help me with that.

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      5 years ago

      amitchandak  in the above dax i think we forgot to add the other two filter, one is select only opening in Active and second is we have to select the previous month of the report date in Inactvie and based on the previous month we have to lookup from Active. 
      that means if in Inactive table the report date is Feb - 1- 2021 then we have to select Jan -1 -2021 in Active table and filter only Opening and then based on the EMPID we have to select the L2 from Active.