cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Working with Filters and Lastnonblank

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   
ticketdateusernamecomment
T1235/4/2020bobwhat is the status
T1235/2/2020waldoinvestigating
T1234/29/2020bobplease see attachment
T1235/5/2020auto 
T1234/15/2020auto 
T3455/2/2020mikeCan I get an update
T3455/4/2020shawnneed more info
1 ACCEPTED SOLUTION
Super User IV
Super User IV

maybe

 

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

RETURN

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Community Support
Community Support

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.

5.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

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

Super User IV
Super User IV

@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 )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

maybe

 

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

RETURN

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors