Measure that calculates earliest date that satisfies several conditions.

[JoeCrozier]

I have data that looks like this:

I need to know the 'modification date' for each protocol (if it exists, not every protocol will have one).  You may notice 'modification date' is not a column or response.  Its just something that I, the end-user human would know.

When a protocol_no has two rows (for one irb committee parent) that are both "Initial Review" and "other", I know the first action date is the "modification date".  I.e. if a protocol does not have two rows with "initial review", it doesn't have one.  If it has two rows with "initial review" but only one of those rows says "other", it doesn't have one.   If it has two rows that say "initial review" and "other", but each row has a different "irb_committee_parent", it doesn't have one.

BUT if a protocol has two rows with the same committee parent, and both say "initial review" and "other", bingo.  It had a modification date and the first "action date" is what I'd like to return.  Picture below of desired results. (red wouldn't be enough info, blue would fulfill the conditions)

I'd love to create a measure that could give me that particular date so i could include it in rows of a table on the dashboard.

I've tried to figure this out using chatgpt, but it gave me this result, with this error.

[amitchandak · Accepted solution]

@JoeCrozier , You can try like

New column =
CALCULATE(
MIN('Table'[Action Date]),
FILTER(
'Table','Table'[Action Type] = "Initial Review" && ,'Table'[Protocol_no] = earlier([Protocol_no] )))

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

or

Meausre

Modification Date =
CALCULATE(
MIN('Table'[Action Date]),
FILTER(
'Table',
'Table'[Action Type] = "Initial Review" &&
'Table'[Other Column] = "other" &&
COUNTROWS(
FILTER(
'Table',
'Table'[Action Type] = "Initial Review" &&
'Table'[Other Column] = "other"
)
) = 2 &&
COUNTROWS(
VALUES('Table'[irb_committee_parent])
) = 1
)
)

or

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[JoeCrozier]

Thank you for this!  The measure APPEARS to work.  Allow me to do some spot-checking for accuracy and within a day or so I'll probably accept this as the solution.

Of note, the column does not work at all.  Here's what I put in (with appropriate column names.  Also I see no mention of "IRB_Committee_parent" in your column suggestion): 

And here's the results it gave me for literally every single row of the entire dataset:

But like I said, the measure looks promising.  Assuming it works and everything looks good after I test it for accuracy, quick question for you (unless you think I should make new question):

For some studies, there might be like 5 rows that fit the condition of "initial review", "other" and the same "irb_parent_committee".
I might need to make other measures that would catch the second earliest date for instance, or maybe the third earliest.  Is it fairly easy to modify your measure to do that?