I am struggling with getting only the last non blank value for each ticket.
In the Power Pivot measure, I have used a function LASTNONBLANK() , but the outcome was not reliable - probably because data model does not consider the original order of rows.
Excel formula or VBA is not an option and I think that in Power Query it will not be very efficient.
| ticket | step | timestamp | value |
|---|---|---|---|
| 1 | 1 | 7/28/2021 04:15 PM | |
| 1 | 2 | 7/28/2021 04:23 PM | a |
| 1 | 3 | 7/28/2021 04:30 PM | |
| 1 | 4 | 7/28/2021 04:37 PM | b |
| 1 | 5 | 7/28/2021 04:44 PM | |
| 1 | 6 | 7/28/2021 04:51 PM | |
| 1 | 7 | 7/28/2021 04:59 PM | |
| 2 | 1 | 7/29/2021 01:51 PM | a |
| 2 | 2 | 7/29/2021 02:49 PM | b |
| 2 | 3 | 7/29/2021 03:47 PM | c |
| 2 | 4 | 7/29/2021 04:44 PM | |
| 2 | 5 | 7/29/2021 05:42 PM | a |
| 2 | 6 | 7/29/2021 06:39 PM |
For ticket "1", the returned value should be "b" (step 4) and for ticket "2", the returned value should be "a" (step 5).
| ticket | value |
|---|---|
| 1 | b |
| 2 | a |
PBI community helped me to create the Power Pivot measure with 2 different solutions, both working smoothly.
VAR _lasttimestampnonblank =
CALCULATE ( MAX ( Data[timestamp] ), Data[value] <> BLANK () )
RETURN
IF (
HASONEVALUE ( Data[ticket] ),
CALCULATE ( VALUES ( Data[value] ), Data[timestamp] = _lasttimestampnonblank )
)
and a second solution:
Measure 2 =
VAR __Table = FILTER('Table7',[value]<>"")
VAR __Max = MAXX(__Table,[timestamp])
RETURN
MAXX(FILTER(__Table,[timestamp] = __Max),[value])