Forum Discussion

cbruhn42's avatar
cbruhn42
Helper III
1 year ago
Solved

Return Earliest Date/Time Based on Calculated Column

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.

 

VesselIDDTAmountin SpecColumn
640078/13/2024 19:508408/13/2024 19:50
640078/14/2024 9:007008/13/2024 19:50
640078/14/2024 21:305408/13/2024 19:50
640078/15/2024 9:005108/13/2024 19:50
640078/15/2024 20:303518/13/2024 19:50
640078/19/2024 11:113518/13/2024 19:50
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

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

     

     

    Here is a slightly better alternative with better sample data

     

     

     

  • parry2k's avatar
    parry2k
    1 year ago

    cbruhn42 or try this:

     

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

     

     

    Here is a slightly better alternative with better sample data

     

     

     

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    cbruhn42 or try this:

     

    Earliest Date = 
CALCULATE ( 
    MIN ( 'Vessel'[DT] ),
    ALLEXCEPT ( 'Vessel', Vessel[VesselID] ),
    Vessel[in Spec] = 1 
)
    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      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.