Calculated Column: Distinct id# should return first date and ignore other dates

Question

I have the requirement in which id# has duplicate records and also has duplicate received_date, where I need to show only unique received date for each id#. Could you please help me on how to resolve this?

Data sample shown below:

I have tried the following in the calculated column

expected_date_or_result =
VAR selected_id = test[id#]
VAR distinct_received_date =
    CALCULATE (
        FIRSTDATE ( test[received_date] ),
        FILTER ( test, test[id#] = selected_id )
    )
RETURN
    distinct_received_date

I am not sure now to add blanks in case of duplicate received_date.

Please help me with this.

Note: I cannot use remove duplicate option since it is affecting my column group

Answer 1

There are likely many ways to approach this but here's the first one that comes to my mind:

expected_date_or_result =
VAR TopRow =
    TOPN (
        1,
        FILTER ( test, test[id#] = EARLIER ( test[id#] ) ),
        test[received_date], ASC,
        test[group], ASC
    )
RETURN
    MAXX (
        FILTER ( TopRow, test[group] = EARLIER ( test[group] ) ),
        test[received_date]
    )

This picks the top row of the table filtered by id# and sorted by received_date and group and then filters that row so that it's only non-empty if the group is the top one and extracts the received_date column using MAXX.