Get last value by selected dimesnion

[Micki]

Hello, 

I have  a table:

Plan	Level 1	Level 2	Title	LastResultOutcome	Date
Plan 1	TestPool	Events	Histogram	None	07-10-20 7:35
Plan 2	TestLab	Events	Histogram	None	09-10-20 12:44
Plan 2	TestLab	Events	Histogram	Passed	09-10-20 12:44
Plan 2	TestLab	Events	Histogram	Passed	12-10-20 12:24
Plan 2	TestLab	SI_01	Histogram	None	30-10-20 10:04

And I need to get last value(LastResultOutcome) based on selected dimension. for example, if I select from dimension [Plan] =[Plan 2], following result should be like that:

Plan	Level 1	Level 2	Title	LastResultOutcome	Date
Plan 2	TestLab	SI_01	Histogram	None	30-10-20 10:04

If if I select from dimension [Plan] =[Plan 2] and [Level 2] = [Events], following result should be like that:

Plan	Level 1	Level 2	Title	LastResultOutcome	Date
Plan 2	TestLab	Events	Histogram	Passed	12-10-20 12:24

What i've done: 

Aggr(FirstSortedValue(,-Date),[Plan]) 

FirstSortedValue(aggr([LastResultOutcome],Date,[Plan]),-Date)

And nothing is working 😞

@sunny_talwar 

Thank you so much in advance !

[Gabriel]

@Micki 

Maybe try this 2 functions

MAXSTRING(LastResultOutcome) 

MINSTRING(LastResultOutcome)

[Micki]

@Gabriel, thanks but it need to be aggregated  by Plan and based on Date 

[Chanty4u]

it should work. try below

FirstSortedValue([Plan],-Date)

or

FirstSortedValue(Aggr(Plan), Date), -Date)

[agigliotti]

Hi @Micki ,

Maybe this :

=FirstSortedValue( LastResultOutcome, -Date )

as expression to get the last value  of  "LastResultOutcome" field  based on "Date" field.
"Plan" field as dimension.

I hope it helps.

Andrea

[Micki]

Hi, @agigliotti , @Chanty4u ,  based on your suggestions i did expression like FirstSortedValue(Aggr(LastResultOutcome,Title), -[Date]) that was working partially 

As you see measures should show also ResultOutcome in area where i mark as yellow, but it's not , honestly i don't know how to aggr() Title column more or else 

[agigliotti]

for Title as a calculated dimension you could use the below expression:
=aggr( FirstSortedValue( Title, -Date ), Plan )
OR
could you share a sample app with mock data to look at?

[Micki · Accepted solution]

@agigliotti it's done i add just ditinct to my expression, FirstSortedValue( distinct Aggr(LastResultOutcome,Title), -[Date])