Selecting max value for each row with the same condition

[bleow]

I have the following data:

ID	Date
01	4/5/2020
01	null
02	1/6/2021
03	2/3/2021

I want to create a second column Date2 that states the max date for each ID, like so:

ID	Date	Date2
01	4/5/2020	null
01	1/1/9999	null
02	1/6/2021	1/6/2021
03	2/3/2021	2/3/2021

Is there a solution for this that doesn't involve Group By? To elaborate, my current method for doing this is:

1. Replace null with a date far far into the future, e.g. 1/1/9999
2. Duplicate the data table
3. Group the duplicate table by max of Date to get Date2, which will ensure 1/1/9999 is selected should it exist for each Misc Value
   1. 
4. Merge the original with the duplicate
   1. 
5. Replace 1/1/9999 back with null

But I'm not really a fan of this rudimentary technique because it involves so many steps. Does a simpler, formula-based solution exist?

[Anonymous]

Hi @bleow ，

You can create a measure as below to get it:

Date2 = 
VAR _count =
    COUNTX (
        FILTER (
            ALLSELECTED(  'Table' ),
            'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
                && ISBLANK ( 'Table'[Date] )
        ),
        [ID]
    )
RETURN
    IF (
        _count > 0,
        BLANK (),
        CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    )

Best Regards

[DataInsights · Accepted solution]

@bleow,

Try this calculated column (DAX):

Date2 = 
VAR vMaxDateNoBlank =
    CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
VAR vRowCountWithBlank =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        ISBLANK ( Table1[Date] )
    )
VAR vResult =
    IF ( vRowCountWithBlank > 0, BLANK (), vMaxDateNoBlank )
RETURN
    vResult