Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Leo8542
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
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.