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

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors