Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
maybe
VAR __last = MAXX(FILTER('Table',NOT(ISBLANK('Table'[comment])),[date])
RETURN
MAXX(FILTER('Table',[date]=__last),[comment])
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.
For some reason this one I couldn't get it working.
@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 )
maybe
VAR __last = MAXX(FILTER('Table',NOT(ISBLANK('Table'[comment])),[date])
RETURN
MAXX(FILTER('Table',[date]=__last),[comment])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |