Remove duplicate value but keep all the reste of the row

[Pingouin_Puni]

Hi all,

I'm trying to remove certain duplicates in specific a column but keep all the rows. I'm working on a calculated table and the second red rectangle is what I want to achieve:

The 1st column contains IDs.

I hope for the red result if the IDs AND the values the green rectangle are duplicates. For the future: I might have more than 2 ID and green rectangle values as duplicates.

If it helps, the removing can be based on the penultimate colomn, which is a date. The problem is that those dates can be duplicates too...

Do you think it's possible through DAX?

Regards,

Pingouin Puni

[Mariusz]

Hi @Pingouin_Puni 

This should give you the desired result.

Column = 
VAR __firstDate = 
    CALCULATE( 
        MIN( 'Table'[penultimate] ), 
        ALLEXCEPT( 'Table', 'Table'[Mission en cours_3.2] ) 
    ) 
RETURN 
IF( 'Table'[penultimate] = __firstDate, 'Table'[Dispo par mission] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Pingouin_Puni]

Hi @Mariusz ,

Thank you for answering !

Your solution worked, but not as I intended to. I realized I didn't give the whole picture so I'll try to give as much info as I can through the attached .pbix and the following :

The formula for "Test" is this one :

Test = 
IF(
    Test[Nb of ID]>1;
    IF(
        Test[State of the mission]=Test[Mission_Test (groupes)];
        Test[Avaliability];
        BLANK()
    );
    Test[Avaliability]
)

For a duplicate ID :

1. If there is 1 "Ongoing" status of the mission, in the "Test" column I need to have value of "Availability" the cell.
2. If there are several "Ongoing", I need to keep them as well in the cell. (in green, all the column used in this case);
3. If the state of the mission is not "Ongoing", in the "Test' column I need to have the value of "Availability" that is in the earliest "Stat" date column. And still keep the row (as presented in the red rectangle).

I hope I explained it clearly this time, let me know if I didn't 🙂

Regards,

Pingouin Puni