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.
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 |
Solved! Go to Solution.
Please check if your date column's data type is Text? If so, please convert it to date and try again.
Proud to be a Super User!
@Sharma0815 , try like
new measure =calculate(max(Table[RSA_Approv_Decis_Date]),allexcept(Table[Case]))
You can add additional condition
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])
Proud to be a Super User!
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.
Proud to be a Super User!
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
Please check if your date column's data type is Text? If so, please convert it to date and try again.
Proud to be a Super User!
Thanks alot ,appreciated
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |