Forum Discussion

bastik's avatar
bastik
Frequent Visitor
4 years ago

Latest date and Condition/filter

Hi, 

I have the following data set and I would like to get the most recent items with a specific status (e.g Code review)

Ticket IDStatusLast Updated
Key-1To Do1/1/2022 11:32:12
Key-1In Progress1/1/2022 12:32:12
Key-1To Do1/2/2022 11:32:12
Key-1In Progress1/2/2022 14:32:12
Key-1Code Review1/2/2022 15:32:12
Key-1Closed1/2/2022 16:32:12
Key-2To Do1/1/2022 11:32:12
Key-2In Progress1/1/2022 12:32:12
Key-2To Do1/2/2022 11:32:12
Key-2In Progress1/2/2022 14:32:12
Key-2Code Review1/2/2022 15:32:12

 

I've tried =CALCULATE (MAX([Last Updated]), FILTER(Table,Table[Status] = 'Code Review') but that brings incorrect data for what I want, as it also retrieves items that have had that status in the past. For example: if KEY-1 was in Code Review twice, the formula above will bring the MAX date of the two, and include KEY-1 in the measure

I only want the items that are now/or most recent in Code Review

I'm not sure if MAX or LASTDATE can solve this.

Any ideas? Any help would be kindly appreciated. 

Need Help

2 Replies

  • AlexanderPrime's avatar
    AlexanderPrime
    Solution Supplier
    4 years ago

    Are you looking to get this as a measure to go on the table provided in your original post? Or are you just after another table list that shows the latest date for a code review on each ticket ID?

     

    If it's the latter, no DAX would be needed, you could just make a new table with these 3 columns. Change the "Last Updated" Values on the visual to show "Latest" by clicking on the dropdown arrow for it,. Then filtering the visual to show only Code Review. 

    (Made a slight change on your provided data to show Key-2's Code Review a minute later than Key-1's to show it is picking up a different one).

     

     

    • bastik's avatar
      bastik
      Frequent Visitor
      4 years ago

      I'm looking to display this as a single card and distributed in time. 

      The above wouldn't work as Key-1's latest status is not Code Review but Closed. So from my table, I would need to bring back only Key-2 since it's latest status is 'Code review'. 

      I don't need the latest date of 'Code Review', I need the items that have their last status 'Code Review'.