Forum Discussion

Leo8542's avatar
Leo8542
Frequent Visitor
9 years ago
Solved

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  | ...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Leo8542,

     

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

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

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Leo8542,

 

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

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

 

Regards

Leo8542's avatar
Leo8542
Frequent Visitor
9 years ago

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