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

 

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 )
    )
    • satlasg's avatar
      satlasg
      Helper 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 )
    )

       

  • satlasg's avatar
    satlasg
    Helper I
    8 years ago

    Hi Greg_Deckler this formula treats memory better, however it does not bring a result, PowerBI desktop only displays "Working on it" and stucks. On the task manager i see it does not affect the memory that much. The CPU goes to 25-26% for the process MSFT SQL Server Analysis Services and keeps working. Cannot yet figure out what might be wrong.

     

    @smoupre wrote:

    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

       

      Try this as a MEASURE in table B

       

      Value latest =
VAR RecentDate =
    CALCULATE ( MAX ( TableA[Date] ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableA[Value], 1 ),
        FILTER ( TableA, TableA[Date] = RecentDate )
    )
      • nickchobotar's avatar
        nickchobotar
        Skilled Sharer
        8 years ago

        satlasg

        Could you please try my version.  It appears you have a ton of rows, so I am swapping iteration with the set logic.
        By the way,  the code works both as calc column and a measure.

         

        =
CALCULATE (
    MAX ( Table1[Value] ),
    INTERSECT ( 
    		VALUES ( Table1[ID] ), 
    		VALUES ( Table2[ID] ) 
	)
)

         

         

        Thanks, Nick -