Forum Discussion
Microsoft EmployeeFiltering a table to a new table
I am not really sure how to go about an issue I came across.
I have a table called status containing columns such as:
| Project ID | Status Extract | Status Change Date | Created date |
| 13568 | 3.0 | 7/15/20 | 7/10/20 |
| 13568 | 4.0 | 7/16/20 | 7/10/20 |
| 15598 | 1.0 | 7/18/20 | 7/16/20 |
| 15598 | 2.0 | 7/20/20 | 7/16/20 |
| 13568 | 3.0 | 7/20/20 | 7/10/20 |
| 13772 | 4.0 | 7/30/20 | 7/10/20 |
I am trying to filter this table into another table or create another column in this table which will allow me to filter out blanks. The project may have the same status twice but my goal is to have a project with no duplicate status and take the most recent date for the status changed date.
So a a custom column would look like this. Where the first row with the new column is left blank because there is a more recent date for that status with that project ID.
| Project ID | Status Extract | Status Change Date | Created date | Edit Status Change Date |
| 13568 | 3.0 | 7/15/20 | 7/10/20 | |
| 13568 | 4.0 | 7/16/20 | 7/10/20 | 7/16/20 |
| 15598 | 1.0 | 7/18/20 | 7/16/20 | 7/18/20 |
| 15598 | 2.0 | 7/20/20 | 7/16/20 | 7/20/20 |
| 13568 | 3.0 | 7/20/20 | 7/10/20 | 7/20/20 |
My other idea fold be to somehow pull this data into a new table with a filter but I don't now how I would do that.
I'm not totally sure I follow the goal, but if you're familiar with Lag and Lead in SQL, is the idea that if there's a Lead value on the date, to leave the column blank? If so I think this is what you're looking for. Still new myself but I used this a while ago and I think this will work if I follow ask correctly:
https://community.powerbi.com/t5/Desktop/Lead-and-Lag-in-DAX/td-p/649162
2 Replies
W_G_RyanHelper I
I'm not totally sure I follow the goal, but if you're familiar with Lag and Lead in SQL, is the idea that if there's a Lead value on the date, to leave the column blank? If so I think this is what you're looking for. Still new myself but I used this a while ago and I think this will work if I follow ask correctly:
https://community.powerbi.com/t5/Desktop/Lead-and-Lag-in-DAX/td-p/649162
- ConnorH
I was able to pull the Latest date where ProjectID=ProjectID and Status=Status. I still do not know how to set this equation up to only keep one value per status per project ID.
ProjectID Status Extract Modified Last Status Date (New column) 34631 2.1 6/1/20 34631 2.1 6/4/20 6/4/20 34631 4.0 6/20/20 6/20/20 "Last status date =var _ProjectID = 'Status'[ProjectID]var _Status = 'Status'[Status Extract]Var _StatusDate = 'Status'[Modified]var _LaterDate = MAXX(FILTER('Status','Status'[ProjectID] = _ProjectID && 'Status'[Status Extract] = _Status), 'Status'[Modified])Return _LaterDate"this equation will make the table like this:ProjectID Status Extract Modified Last Status Date (New column) 34631 2.1 6/1/20 6/4/20 34631 2.1 6/4/20 6/4/20 34631 4.0 6/20/20 6/20/20
