Working with Filters and Lastnonblank

[ddurosier]

Hi all, was hoping someone can quickly help because I've been trying to figure this out yesterday. I have some extra filters I want to add but just want a head start on how to get that going. Basically, I'm trying to do a filter to grab the latest comment of the ticket that's blank. My output should show for ticket T123 - the comment by Bob - "what is the status" instead of the one by Auto. Does that make sense?

Lastcomment = LASTNONBLANK( TableA[date], 1 ),

TABLE A
ticket	date	username	comment
T123	5/4/2020	bob	what is the status
T123	5/2/2020	waldo	investigating
T123	4/29/2020	bob	please see attachment
T123	5/5/2020	auto
T123	4/15/2020	auto
T345	5/2/2020	mike	Can I get an update
T345	5/4/2020	shawn	need more info

[v-jayw-msft]

Hi @ddurosier ,

Please check the formula below.

Measure = 
var maxdate = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[comment]<>BLANK()))
return
IF(SELECTEDVALUE('Table'[date])=maxdate,1,0)

Add this formula to visual filter and the result will be shown as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ddurosier]

For some reason this one I couldn't get it working.

[amitchandak]

@ddurosier , create a measure like this and use in analysis

Measure =
VAR __id = MAX ( 'Table'[ticket] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[ticket] = __id , isblank(Table[comment]))
RETURN CALCULATE ( count ( 'Table'[ticket] ), VALUES ( 'Table'[ticket ), 'Table'[ticket] = __id, 'Table'[date] = __date )

[Greg_Deckler · Accepted solution]

maybe

VAR __last = MAXX(FILTER('Table',NOT(ISBLANK('Table'[comment])),[date])

RETURN

MAXX(FILTER('Table',[date]=__last),[comment])

[ddurosier]

I was able to get the result with this. Now, if I wanted to add additional filters such as "if comment table contains this" or "only if reporter is this person etc.", how would I incorporate that to this measure? Is not a big deal but was just curious.

[Greg_Deckler]

So,

VAR __last = 
  MAXX(
    FILTER(
      'Table',
      ( NOT(ISBLANK('Table'[comment]) || <some other condition> ) && <this condition also>
    ),
    [date]
  )
RETURN
  MAXX(FILTER('Table',[date]=__last),[comment])

It's just logic that you can string together with || for OR and && for AND and you can use parens ( ) to group things.