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 ),
|T123||5/4/2020||bob||what is the status|
|T123||4/29/2020||bob||please see attachment|
|T345||5/2/2020||mike||Can I get an update|
|T345||5/4/2020||shawn||need more info|
Solved! Go to Solution.
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.
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 , create a measure like this and use in analysis
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 )
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.
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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.