Apply a value to every duplicated ID

[Pingouin_Puni]

Hi all,

I am having trouble to apply a particular value for a row if the id number is a duplicate. I want to apply a value based on a calculated colomn (state of the mission, which is based on the begening and ending dates of a mission).

By the way, i already have identify duplicates thanks to this formula, which gives me a result >1 if ID is a duplicate :

Duplicates =

Var Matricule = [ID]

RETURN

CALCULATE(

COUNTROWS(Ajouter1);

ALL(Ajouter1);

Ajouter1[ID] = ID

)

What i have :

Id	State of the mission
m9306	Finished
m9306
m9306	In progress

What I want to show :

Id	State of the mission
m9306	In progress

Any idea on how to achieve that ?

Regards

Pingouin Puni

[v-zhenbw-msft · Accepted solution]

Hi @Pingouin_Puni ,

We can create a calculate column to meet your requirement.

Column = 
VAR max_date =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( 'Table', 'Table'[id] = EARLIER ( 'Table'[id] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[State of the mission] ),
        FILTER (
            'Table',
            'Table'[id] = EARLIER ( 'Table'[id] )
                && 'Table'[date] = max_date
        )
)

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pingouin_Puni]

Hello @v-zhenbw-msft ,

This last solution with a calculated colomn worked perfectly and answered my need. I just replaced dates with the priority idea of @camargos88 , so thank you both of you 🙂

Regards,

Pingouin Puni

[camargos88]

Hi @Pingouin_Puni ,

How are you ordering the State of missing column ?

You can give them a numerical values (by priority). 

After that you can get the last value per id.

Does that help you ?

Ricardo

[Pingouin_Puni]

Thank you for answering @camargos88 ,

This are the different sates of mission i have and i gave them a priority value:

Status of the mission	Priority
No mission	0
Mission to come	0
Finished	0
In progress	1

As for the last value par ID, do you know what would be the dax formula ? I'm guessing MAX( ) would be involved but I can't figure it out. I'm still new on DAX, but learn every day 🙂

Pingouin Puni

[camargos88]

Hi @Pingouin_Puni ,

Just related both tables by status and create this measure:

Measure =

VAR _priority = MAXX(SUMMARIZE('Table'; 'Table'[Id]; "Max"; MAX(Priority[Priority])); [Max])

RETURN CALCULATE(DISTINCT(Priority[Status of the mission]); FILTER(Priority; Priority[Priority] = _priority))

Also if necessary, you can create a new table:

T = ADDCOLUMNS(SUMMARIZE('Table'; 'Table'[Id]; "Max"; MAX(Priority[Priority])); "Status"; CALCULATE(DISTINCT(Priority[Status of the mission]); FILTER(Priority; Priority[Priority] = [Max])))

Ricardo