Forum Discussion

Clint's avatar
Clint
Icon for Helper V rankHelper V
5 years ago
Solved

Finding the right value using Lastnonblank

Just when I thought I was getting a handle on LastNonBlank, I'm not 

 

Per the embedded table below, shouldn't this DAX return the highlighted value and not the one above it?
Latest Comment =
LASTNONBLANK('Burndown 2'[Comments],MAX('Burndown 2'[Week])
)
What I expected to happen is the DAX to read thru the rows, find the first non blank comment field based on the max week date; which should return the second comment and not the first one.  Instead, it returns the first one...
 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Clint , Try like

     

    lastnonblankvalue(Table[Week],max(Table[Comment]))

    or

    calculate(lastnonblankvalue(Table[Week],max(Table[Comment])), allexcept(Table, Table[project]))

    or

    calculate(lastnonblankvalue(Table[Week],max(Table[Comment])), filter(allselcted(Table), Table[project]=max( Table[project])))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Clint , Try like

     

    lastnonblankvalue(Table[Week],max(Table[Comment]))

    or

    calculate(lastnonblankvalue(Table[Week],max(Table[Comment])), allexcept(Table, Table[project]))

    or

    calculate(lastnonblankvalue(Table[Week],max(Table[Comment])), filter(allselcted(Table), Table[project]=max( Table[project])))

    • Clint's avatar
      Clint
      Icon for Helper V rankHelper V
      5 years ago

      amitchandak ,

       

      Thanks Amit.  This worked: lastnonblankvalue(Table[Week],max(Table[Comment])) but I don't understand why?  The week column should never have a blank in the field as it is populated from the start of a project to the thend of the project.  it is the comment field that will have blanks in the column and I need to always extract the latest comment; which is always the last non-blank in that row as determined by max week date