Forum Discussion
How could I transform the table based on date and specific column at the same time with DAX?
Hi, All,
I have a dataframe as follow. There are 2 players' training records.
For calculating and present the current completion status, I have to filter the current table based on the Status and Latest Date.
Status: Only keep the single completed activity and incomplete activity. If the activity is completed and have duplicate name. Just remove it.
Completion Date: Just keep the latest completion date.
Is there any way I could conver the Table A to Table B? Thank you.
Table A
| Player | Training ID | Activity Name | Completion Date | Status |
| A | 23F | Football | 3/2/2023 | Completed |
| A | 22F | Football | 9/1/2022 | Completed |
| A | 23B | Baseball | -- | Incompleted |
| A | 22B | Baseball | -- | Incompleted |
| A | 23T | Tennis | -- | Incompleted |
| A | 22T | Tennis | -- | Incompleted |
| B | 23F | Football | 4/2/2023 | Completed |
| B | 22F | Football | -- | Incompleted |
| B | 23B | Baseball | -- | Incompleted |
| B | 22B | Baseball | -- | Incompleted |
Table B
| Player | Activity Name | Completion Date | Status |
| A | Football | 3/2/2023 | Completed |
| A | Baseball | -- | Incompleted |
| A | Tennis | -- | Incompleted |
| B | Football | 4/2/2023 | Completed |
| B | Baseball | -- | Incompleted |
Hi Steven_Wu,
You could try creating a new table in Table Tools via this code:
In plain text:
Table = SUMMARIZE ( data, [Player], [Activity Name], "Completion Date", MAX ( data[Completion Date] ), "Status", IF ( "Completed" IN VALUES ( data[Status] ), "Completed", "Incompleted" ) )Best Regards,
Alexander
2 Replies
Hi Steven_Wu,
You could try creating a new table in Table Tools via this code:
In plain text:
Table = SUMMARIZE ( data, [Player], [Activity Name], "Completion Date", MAX ( data[Completion Date] ), "Status", IF ( "Completed" IN VALUES ( data[Status] ), "Completed", "Incompleted" ) )Best Regards,
Alexander
Thank you, Alexander,
it's what I need. Thank you so much~
