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 have a data set that looks like the following:
ID | Status | Date
A-1 | In Progress | 7/29/2017
A-1 | Not Started | 7/29/2017
A-2 | In Progress | 7/25/2017
A-2 | Not Started | 7/23/2017
A-3 | In Progress | 7/27/2017
A-3 | Not Started | 7/4/2017
A-4 | Completed | 7/1/2017
A-4 | In Progress | 6/25/2017
A-4 | Not Started | 6/20/2017
A-5 | Completed | 7/5/2017
A-5 | In Progress | 7/1/2017
A-5 | Not Started | 6/30/2017
I want to be able to filter this data to only get the latest record for each ID for a given week (in this example: 7/23/2017-7/29/2017). I have created a calculated table to get just those records we want for the given week to get the following:
ID | Status | Date
A-1 | In Progress | 7/29/2017
A-1 | Not Started | 7/29/2017
A-2 | In Progress | 7/25/2017
A-2 | Not Started | 7/23/2017
A-3 | In Progress | 7/27/2017
A-3 | Not Started | 7/4/2017
However, I come into an issue where there would be multiple records for the same ID for the same Date (for example ID A-1), so ultimately I would want to choose the first record shown, so that my dataset will look like this:
ID | Status | Date
A-1 | In Progress | 7/29/2017
A-2 | In Progress | 7/25/2017
A-3 | In Progress | 7/27/2017
We have tried to use SUMMARIZE, FILTER, and LATESTDATE functions, however I am at a loss as to how it should be utilized. Any help is appreciated. Thanks.
Solved! Go to Solution.
Hi @Leo8542,
Based on my test, you should be able to use the formula below to create a new calculate table in your scenario.
Table = SUMMARIZE ( Table1, Table1[ID], "Status", FIRSTNONBLANK ( Table1[Status], 1 ), "Date", MAX ( Table1[Date] ) )
Regards
Hi @Leo8542,
Based on my test, you should be able to use the formula below to create a new calculate table in your scenario.
Table = SUMMARIZE ( Table1, Table1[ID], "Status", FIRSTNONBLANK ( Table1[Status], 1 ), "Date", MAX ( Table1[Date] ) )
Regards
Thanks, this is the solution I needed. Works perfectly and is easy to understand.
You could create a separate table that has your Status with an ID and create a relationship between the two tables. The StatusID could be something like 1,2,3 (1=Not Started, 2=In Progress, 3=Completed). Then you could get the Max(StatusID) for a given ID and Date.
Table (Status)
ID Name
1 Not Started
2 In Progress
3 Completed
Thanks for the suggestion, but I also think that it might be the case where an entry can go from "Completed" back to "In Progress" so pulling the max may not necessarily give me the most recent status per ID.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |