Forum Discussion

satlasg's avatar
satlasg
Helper 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
satlasg's avatar
satlasg
Helper I
8 years ago

It brings a result, but a date, not a Value that is needed, for the most recent date.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Apologies:

 

Column = LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],MAXX(RELATEDTABLE(IDValues),[Date]))
  • satlasg's avatar
    satlasg
    Helper I
    8 years ago

     

    Column = LOOKUPVALUE('IDValues'[Value],'IDValues'[Date],MAXX(RELATEDTABLE(IDValues),[Date]))

     

    Dear Greg_Deckler

     

    While that seem to work on the small tables i provided, in the large scale tables (>500K rows) it cannot calculate, says "working on it" and after a while a message "out of memoy, please try later" appears.

    Any turnaround please?

    • Greg_Deckler's avatar
      Greg_Deckler
      Community 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
        Community 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 )
    )