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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • Leo8542's avatar
    Leo8542
    Frequent Visitor
    9 years ago

    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.