Pivot Max date with final status of case

[Sharma0815]

 I am working on dataset where each case contains multiple rows with the status that it went through,i am trying to pivot the max(time_stamp) of my final_decision_time and the final_status of case by the Group.

here is my data looks like,

Case	WCF_Approv_Decis	WCF_Approv_Decis_Date	CSA_Approv_Decis	CSA_Approv_Decis_Date	RSA_Approv_Decis	RSA_Approv_Decis_Date	FSB_Approv_Decis	FSB_Approv_Decis_Date
386646		NULL		NULL	Approved	3/3/2020 15:24		NULL
386646		NULL	Approved	2/27/2020 16:37		NULL		NULL
386646	Rejected	2/19/2020 17:30		NULL		NULL		NULL
397142		NULL		NULL	Approved	5/29/2020 18:58		NULL
397142		NULL	Approved	5/29/2020 18:34		NULL		NULL
397142	Rejected	5/29/2020 13:40		NULL		NULL		NULL

I am trying to get an extra columns with the final_decision_date with status of the case like below.

Any way that i can pivot this data by using @dax

Case	WCF_Approv_Decis	WCF_Approv_Decis_Date	CSA_Approv_Decis	CSA_Approv_Decis_Date	RSA_Approv_Decis	RSA_Approv_Decis_Date	FSB_Approv_Decis	FSB_Approv_Decis_Date	Fina_Decision	Final_Decision_Date
386646		NULL		NULL	Approved	3/3/2020 15:24		NULL	Approved	3/3/2020 15:24
386646		NULL	Approved	2/27/2020 16:37		NULL		NULL	Approved	3/3/2020 15:24
386646	Rejected	2/19/2020 17:30		NULL		NULL		NULL	Approved	3/3/2020 15:24
397142		NULL		NULL	Approved	5/29/2020 18:58		NULL	Approved	5/29/2020 18:58
397142		NULL	Approved	5/29/2020 18:34		NULL		NULL	Approved	5/29/2020 18:58
397142	Rejected	5/29/2020 13:40		NULL		NULL		NULL	Approved	5/29/2020 18:58

[amitchandak]

@Sharma0815 , try like

new measure =calculate(max(Table[RSA_Approv_Decis_Date]),allexcept(Table[Case]))

You can add additional condition

[ryan_mayu]

@Sharma0815 

I am not sure if RSA is always the latest status.

you can try below measures.

Column2 = MAXX(FILTER('Sheet11','Sheet11'[Case]=EARLIER(Sheet11[Case])),'Sheet11'[RSA_Approv_Decis_Date])

column 1 = MAXX(FILTER('Sheet11','Sheet11'[Case]=EARLIER('Sheet11'[Case])&&'Sheet11'[Column2]='Sheet11'[RSA_Approv_Decis_Date]),'Sheet11'[RSA_Approv_Decis])

[Sharma0815]

@ryan_mayu  RSA  is not always be the finla status 

[ryan_mayu]

@Sharma0815 

Since lastest update of all the sample data  you provided was RSA related, if RSA is not the final status, then formula of the method I provided will be very long.

check FSB, if FSB is null, then check RSA, if RSA is null, check CSA, if CSA is null ,check WCF.

[Sharma0815]

I will add a conditional statement to get the final status ...,

when i try to use your column2 caluclation, i am getting value comparison error 

Error

DAX comparison operations do not support comparing values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Thanks alot

[ryan_mayu · Accepted solution]

@Sharma0815 

Please check if your date column's data type is Text? If so, please convert it to date and try again.

[Sharma0815]

Thanks alot ,appreciated