Return Earliest Date/Time Based on Calculated Column

[cbruhn42]

I have the data set below and I'm determining the 'in Spec' column by comparing the Amount to the spec limit in a related column.  The column titled 'Column' is trying to return the earliest DT when in Spec column = 1.  This is the DAX I'm using:

MINX(FILTER('DCRS GC Data', 'DCRS GC Data'[in Spec] = 1 && EARLIER('DCRS GC Data'[VesselID])='DCRS GC Data'[VesselID]), 'DCRS GC Data'[DT])

I want to return 8/15/2024 20:30 for this example.

VesselID	DT	Amount	in Spec	Column
64007	8/13/2024 19:50	84	0	8/13/2024 19:50
64007	8/14/2024 9:00	70	0	8/13/2024 19:50
64007	8/14/2024 21:30	54	0	8/13/2024 19:50
64007	8/15/2024 9:00	51	0	8/13/2024 19:50
64007	8/15/2024 20:30	35	1	8/13/2024 19:50
64007	8/19/2024 11:11	35	1	8/13/2024 19:50

[lbendlin · Accepted solution]

Your formula is not overly efficient but it returns the desired result

Here is a slightly better alternative with better sample data

[parry2k · Accepted solution]

@cbruhn42 or try this:

Earliest Date = 
CALCULATE ( 
    MIN ( 'Vessel'[DT] ),
    ALLEXCEPT ( 'Vessel', Vessel[VesselID] ),
    Vessel[in Spec] = 1 
)

[lbendlin]

Looks like your query plan is better with fewer estimated rows but somehow my version is faster.

Edit: Never mind, I had not disabled the query cache.  Same run time, roughly.

[lbendlin · Accepted solution]

Your formula is not overly efficient but it returns the desired result

Here is a slightly better alternative with better sample data