CODEWITHSUNDEEP
powerbidaxpowerquerycalculated-columns
Prem Chand
Prem Chand

Reputation: 126

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

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:

[1]: https://i.sstatic.net/WOYu3.png

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

Upvotes: 1

Views: 605

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Result

Upvotes: 1

Related Questions