Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Lookup for specific value

Hi, I have two table below and would like to know how can I lookup the value base on certain condition. Table1: Date 15/02/2022 21/03/2022 12/04/2022 01/05/2022 11/06/2022 16/0...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question and your datamodel correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file whether it suits your requirement.

     

     

     

     

    Expected result measure: =
VAR _maxdatetable2 =
    MAXX ( FILTER ( Table2, Table2[Date] <= MAX ( Table1[Date] ) ), Table2[Date] )
RETURN
    CALCULATE ( MAX ( Table2[Value] ), Table2[Date] = _maxdatetable2 )

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure if I understood your question and your datamodel correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

 

 

 

 

Expected result measure: =
VAR _maxdatetable2 =
    MAXX ( FILTER ( Table2, Table2[Date] <= MAX ( Table1[Date] ) ), Table2[Date] )
RETURN
    CALCULATE ( MAX ( Table2[Value] ), Table2[Date] = _maxdatetable2 )