Create a new table based on filters

[Anonymous]

I have and e-learning data with duplicates across the different columns just like this

First Name	Last Name	Course title	Status	Completion date
James	Paul	Course A	Registered
James	Paul	Course A	Completed	1/2/18
James	Paul	Course B	Registered
James	Paul	Course B	Completed	3/5/18
Anne	Peter	Course A	Registered
Anne	Peter	Course B	Registered
Anne	Peter	Course C	Registered
Anne	Peter	Course C	Completed	5/8/20

I want to create a new table with columns: [first name], [Last name], [course], [Status] using a DAX expression.

Each person would have one unique row for the completion status of each course.

If a course has been completed, I only want that row on the new table (filtering based on the completion date not blank and if no completion date exist, the status is marked "Incomplete")

For Instance:

First Name	Last Name	Course title	Status
James	Paul	Course A	Completed
James	Paul	Course B	Completed
Anne	Peter	Course A	Incomplete
Anne	Peter	Course B	Incomplete
Anne	Peter	Course C	Completed

[Anonymous · Accepted solution]

Thanks for your response.

I was able to work around it using power query, relationships and DAX. The data was more complex than I explained.

[Anonymous]

@Anonymous 

Create the following Table:

Table 2 = summarize('Table', [first name], [Last name], [Course title],[Completion date],"Status",IF([Completion date]=BLANK(),"Incomplete","Complete"))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , one of the two ways

summarize(filter(Table, Table[Status] ="Completed"  ), [first name], [Last name], [course], [Status] )

or

calculatetable(Table, filter(Table, Table[Status] ="Completed"  ))

[StefD]

Works for me very well too. Is there an opition to have 2 ctriterias listed? as .... {"mm" , "xx"}  ... is not working form me. Thanks in advance.

[StefD]

SOLVED like this:

summarize(filter(Table, Table[Column] = "abc" || Table[Column] = "123") )

[Shadow_BN515]

This worked fantasic, just what I was looking for. Thanks for the help