How to return the earliest date/time among a subset of data in a table

[trcstuart]

Hi, 

This is my first post on this forum so if it can be improved in any way or in the wrong spot please let me know!

I have a table called 'REQUISITION CYCLE' that I created from another table. The relationships for this table look like this:

The 'REQUISITION CYCLE' table includes a field called 'ACTION_DATE' which lists the dates and times that 'actions' happened to the Requisitions. It also includes a field called 'STATUS' which lists the 'actions' that have occured (such as authorized, reviewed, etc.). What I would like to do is to create a new column called 'FIRST REVIEW' to capture the date/time that each Requisition is first reviewed (Requisitions can be reviewed multiple times).

Here is the DAX I have written so far:

Here is the data it is giving me:

The date being returned in the first review column is the earliest for the document but not the earliest for document, for the 'REVIEWED' status.

Any help would be much appreciated!'

Thanks, 

Tyler

[trcstuart]

Thanks @tamerj1 !! Worked perfectly! 

[tamerj1 · Accepted solution]

Hi @trcstuart 

please try

First Review =
MINX (
FILTER (
CALCULATETABLE (
'Requisition Cycle',
ALLEXCEPT ( 'Requisition Cycle', 'Requisition Cycle'[Document_ID] )
),
'Requisition Cycle'[Status] = "REVIEWED"
),
'Requisition Cycle'[Action_Date]
)