FirstSortedValue and Aggr

[SingSing16]

I have issue with creation of dimension including FirstSortedValue as well Aggr.
I already created some measure including value which I am interested but in this case I need to create chart including this values as dimension.

Project_Number	Project_History_Field	Project_History_Value	Historical_field_change_date
P123	Project_Status	Not started	2022-10-11
P123	Username	Lisa M.	2020-12-17
P123	Project_Status	In progress	2023-01-02
P456	Project_Status	On-hold	2023-01-15
P789`	Project_Stage	Review	2020-11-15
P789	Project_Status	In progress	2020-12-28
P789	Project_Status	Cancelled	2021-12-12

I need to gain only one Project_History_Value  according to Project_Status (in Project_History_Field) for single Project_Number according to the newst date.

I tried to create dimension according to this:

=FirstSortedValue({<[Project_History_Field]={"Project_Status"}>}[Project_History_Value],
-Aggr(Max([Historical_field_change_date]),Project_History_Field))

Formally it works but but doesn't if I want to create table or chart with it.

Please help me.

Best Regards

[KGalloway]

Will this work?

I edited your formula slightly and used FirstSortedValue({<[Project_History_Field]={"Project_Status"}>}[Project_History_Value],-[Historical_field_change_date])

[SingSing16]

Hello, 

I created measure  at the beginning excatly the same as you wrote, but I need to create dimension for my chart.

Because of that I tried this with Aggr:

=FirstSortedValue({<[Project_History_Field]={"Project_Status"}>}[Project_History_Value],
-Aggr(Max([Historical_field_change_date]),Project_History_Field)) 

but it does not works on chart.

Any other idea?

[KGalloway]

What about adding fields in the load script to identify the most recent changed record and then apply its status to all of the records for that project as a new field?

raw:
load * inline [
Project_Number, Project_History_Field, Project_History_Value, Historical_field_change_date
P123, Project_Status, Not started, 2022-10-11
P123, Username, Lisa M., 2020-12-17
P123, Project_Status, In progress, 2023-01-02
P456, Project_Status, On-hold, 2023-01-15
P789, Project_Stage, Review, 2020-11-15
P789, Project_Status, In progress, 2020-12-28
P789, Project_Status, Cancelled, 2021-12-12

]
;

left Join
load
Project_Number,
max(Historical_field_change_date) as Most_Recent_Change_Date
resident raw
where Project_History_Field = 'Project_Status'
group by Project_Number;

left join
Load
Project_Number,
Project_History_Value as Most_Recent_Status
resident raw
where Historical_field_change_date = Most_Recent_Change_Date;

This gave me the following table:

[vinieme12]

to create a calculated dimension wrap the expression in Aggr() as below 

=Aggr(FirstSortedValue({<Project_History_Field={'Project_Status'}>} Project_History_Value,-Historical_field_change_date),Project_Number)