Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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 |
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |