Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

DAX LOOKUPVALUE with MAX

Hi,

 

Im trying to lookup a value from another table with MAX

I have two data tables like this:

 

Table1:

Udklip.PNG

Table2:

Udklip3.PNG

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:

 

Udklip2.PNG
 
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
AlB
Super User
Super User

Hi @Anonymous 

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_
    )

 

View solution in original post

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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))

Capture.PNG 

 

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.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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))

Capture.PNG 

 

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.

AlB
Super User
Super User

Hi @Anonymous 

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_
    )

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.