cancel
Showing results for
Did you mean: Helper I

## DAX LOOKUPVALUE with MAX

Hi,

Im trying to lookup a value from another table with MAX

I have two data tables like this:

Table1: Table2: What I'd like to do is, for the empty values in "Table2[Latest Result]" to find the value in "Table1[Result]" based on the highest number in "Table1[Unix]", so i get the latest update from each ID.

So that Table2 ends as: I have tried:

Latest Result = LOOKUPVALUE ( 'Table1'[Result] ; 'Table2'[ID] ; MAX ( 'Table1'[Time] ) )

But gives me all blank, not sure how to fix this.

Anyone that got an idea?

2 ACCEPTED SOLUTIONS Super User III

Hi @MLGOON

Try this for your calculated column in Table2:

```LatestResult =
VAR Latest_Time_ =
CALCULATE (
MAX ( Table1[Time] ),
FILTER ( ALL ( Table1[ID] ), Table1[ID] = Table2[ID] )
)
RETURN
LOOKUPVALUE (
Table1[Result],
Table1[ID], Table2[ID],
Table1[Time], Latest_Time_
)``` Community Support

You can also create a calculate column in table2 using DAX below:

```Latest Result =
VAR Current_ID = Table2[ID]
VAR Max_Unix_Per_ID = CALCULATE(MAX(Table1[Unix]), FILTER(Table1, Table1[ID] = Current_ID))
RETURN
CALCULATE(MAX(Table1[Result]), FILTER(Table1, Table1[Unix] = Max_Unix_Per_ID))``` Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2 Community Support

You can also create a calculate column in table2 using DAX below:

```Latest Result =
VAR Current_ID = Table2[ID]
VAR Max_Unix_Per_ID = CALCULATE(MAX(Table1[Unix]), FILTER(Table1, Table1[ID] = Current_ID))
RETURN
CALCULATE(MAX(Table1[Result]), FILTER(Table1, Table1[Unix] = Max_Unix_Per_ID))``` Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Super User III

Hi @MLGOON

Try this for your calculated column in Table2:

```LatestResult =
VAR Latest_Time_ =
CALCULATE (
MAX ( Table1[Time] ),
FILTER ( ALL ( Table1[ID] ), Table1[ID] = Table2[ID] )
)
RETURN
LOOKUPVALUE (
Table1[Result],
Table1[ID], Table2[ID],
Table1[Time], Latest_Time_
)```  