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.
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.
Join our monthly meetings and learning sessions.
We are excited to announce the Power BI Super Users!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.