Lookupvalue in 3 columns based on same date/key

[vbabka]

Hello,

i am struggeling with this issue in PowerBI.

I am creating headcount tables in my company for attendance rates.

i need to add value of Cell unit  to employees.

I have two tables, unique per each year_month.

1) Cell - including fields:

Year_Month

ID

Cell unit

Key1 (=ID + YEAR_MONTH)

2) HC - including fields:

Year_Month

ID 1 

ID 2

ID 3

KEY1 (=ID1 + YEAR_MONTH)

Both tables are connected via Key1

What i need to do ?

In HC table create calculated column, that returns value of Cell[Cell unit] field, if Cell[ID] is found in HC[ID1] or HC[ID2] or HC[ID3] - but it has to be searched only within each Year_Month (so search Cell[ID] from 2021_01 in HC[ID1]  from 2021_01) and so).

In input data, Cell[ID] can be found only in one column per each row in Headcount Table.

I tried searching and typing different formulas including If, vlookupvalue and so but none of the worked. I got return the values but it was vlookuping them ignoring the Year_Month.

Can anyone help me please ? Thank You

[amitchandak · Accepted solution]

@vbabka , We can create a column like

maxx(filter(Cell, (HC[ID1] =Cell[ID] || HC[ID2] =Cell[ID]   || HC[ID2] =Cell[ID]  ) &&  HC[YEAR_MONTH] =Cell[YEAR_MONTH]  ) ,Cell[Cell unit]) 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[vbabka]

Wow ! incredible - thank you a lot. what a fast answer ! you are really good