Forum Discussion
Create a new table based on filters
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 |
6 Replies
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" ))
This worked fantasic, just what I was looking for. Thanks for the help
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.
SOLVED like this:
summarize(filter(Table, Table[Column] = "abc" || Table[Column] = "123") )
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.
