New column with first occurences of a value in a column with filters

[Charlesdudu]

Hello community,

This is my first post here and  I didn't find on the forum how to resolve my problem.

I have a data source which is structured like this :

I need to create 2 new columns :

• A new column that gives me the value of the column "Valeur" for the first occurrence of "SV" in the column "Type" for each value of the column "Clé train"
• A new column that gives the value of the column "Valeur" for the second occurrence of "EV" in the column "Type" for each value of the column "Clé train"

The result should be "0000108780" for the first column and "0000063881" for the 2nd column for the example given.

I have done plenty of tests with filters functions but don't find a correct approach.

Thank you for your help !

Regards

Charles

[v-zhenbw-msft · Accepted solution]

Hi @Charlesdudu ,

We can add an index column and create three calculate columns to meet your requirement.

1. We need to add an index column in Power Query Editor.

2. Then we close and apply, create a rank column using DAX function.

Column = 
RANKX(FILTER('Table','Table'[TYPE]=EARLIER('Table'[TYPE])),'Table'[Index],,ASC,Dense)

3. At last we can create two columns to get the result.

New column SV = CALCULATE(MAX('Table'[value]),FILTER('Table','Table'[Column]=1&&'Table'[TYPE]="SV"))

New column EV = CALCULATE(MAX('Table'[value]),FILTER('Table','Table'[Column]=2&&'Table'[TYPE]="EV"))

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

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.

BTW, pbix as attached.

[DataInsights]

@Charlesdudu, try these calculated columns:

Valeur First SV = 
VAR vCleTrain = TestData[Cle Train]
VAR vCleTrainTable =
    FILTER ( TestData, TestData[Cle Train] = vCleTrain && TestData[Type] = "SV" )
VAR vResult =
    MINX ( vCleTrainTable, TestData[Valeur] )
RETURN
    vResult

Valeur Second EV = 
VAR vCleTrain = TestData[Cle Train]
VAR vCleTrainTable =
    FILTER ( TestData, TestData[Cle Train] = vCleTrain && TestData[Type] = "EV" )
VAR vRankedTable =
    ADDCOLUMNS (
        vCleTrainTable,
        "Rank", RANKX ( vCleTrainTable, TestData[Valeur],, ASC, DENSE )
    )
VAR vTargetRow =
    FILTER ( vRankedTable, [Rank] = 2 )
VAR vResult =
    MAXX ( vTargetRow, TestData[Valeur] )
RETURN
    vResult