Forum Discussion

satlasg's avatar
satlasg
Icon for Helper I rankHelper I
8 years ago

Get most recent values on new table column

Hello,  I have two tables A & B with "many to one" relationship, as below:                I am looking for a formula to fill in 'Table B'[Value Latest] based on the Value from the more...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

OK, I got rid of the MAXX, see if this works better.

 

Column2 = 
var myMax = CALCULATE(MAX(IDValues[Date]),RELATEDTABLE(IDValues))
var myID = [ID]
RETURN LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],myMax,'IDValues'[ID],myID)
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

satlasg

 

In that case use FirstNonBlank instead of SUM. Here is the revised formula

 

Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )
  • satlasg's avatar
    satlasg
    Icon for Helper I rankHelper I
    8 years ago

    Hi Zubair_Muhammad

     

    Tried that revised one, but again it seems to drain the memory, i am getting the same message and no result.

     

     

    Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )