cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter Report by Max Date and Select First Entry for Multiple Matches

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Filter Report by Max Date and Select First Entry for Multiple Matches

Hi @Leo8542,

 

Based on my test, you should be able to use the formula below to create a new calculate table in your scenario. Smiley Happy

Table = 
SUMMARIZE (
    Table1,
    Table1[ID],
    "Status", FIRSTNONBLANK ( Table1[Status], 1 ),
    "Date", MAX ( Table1[Date] )
)

t1.PNG

 

Regards

View solution in original post

4 REPLIES 4
Highlighted
Resolver II
Resolver II

Re: Filter Report by Max Date and Select First Entry for Multiple Matches

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

Highlighted
Frequent Visitor

Re: Filter Report by Max Date and Select First Entry for Multiple Matches

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.

Highlighted
Microsoft
Microsoft

Re: Filter Report by Max Date and Select First Entry for Multiple Matches

Hi @Leo8542,

 

Based on my test, you should be able to use the formula below to create a new calculate table in your scenario. Smiley Happy

Table = 
SUMMARIZE (
    Table1,
    Table1[ID],
    "Status", FIRSTNONBLANK ( Table1[Status], 1 ),
    "Date", MAX ( Table1[Date] )
)

t1.PNG

 

Regards

View solution in original post

Highlighted
Frequent Visitor

Re: Filter Report by Max Date and Select First Entry for Multiple Matches

Thanks, this is the solution I needed. Works perfectly and is easy to understand.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors