How could I transform the table based on date and specific column at the same time with DAX?

[Steven_Wu]

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

[Steven_Wu]

Thank you, Alexander,

it's what I need. Thank you so much~

[barritown · Accepted solution]

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

My YouTube vlog in English

My YouTube vlog in Russian