Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Lookup with filter

Hi good day,

Can anyone help me on my lookup value. I have two table and i want to look up the value from table 2 but filter by year. Pls refer below.

 

RESULT

Thank you

Need Help
  • Demert's avatar
    Demert
    1 year ago

    Hi AllanBerces ,

     

    What I suggest is creating a new column in table01 and using calculate instead of lookup value. See below:

    Progress2023 = CALCULATE(MAX(Table2[Daily Progress]), Table2[Year]=2023 , Table2[Work]=Table2[Work])

    For every year we are creating a new column to calculate the max value to the corresponding work column and year 

     

2 Replies

  • Demert's avatar
    Demert
    Resolver III
    1 year ago

    Hi AllanBerces ,

     

    What I suggest is creating a new column in table01 and using calculate instead of lookup value. See below:

    Progress2023 = CALCULATE(MAX(Table2[Daily Progress]), Table2[Year]=2023 , Table2[Work]=Table2[Work])

    For every year we are creating a new column to calculate the max value to the corresponding work column and year