How to get latest value if duplicate id exist

[henkka]

Hi,

Can someone please help. I have tried to get latest wage, if multiple workagreements exist without any luck.

The table is below and what I'm trying to achieve is to check if duplicate Employee.id existis, then sumx the wage based from wage.Id (biggest number is the latest). If duplicate not existing then sumx wage.

[henkka]

@johnt75  Great, thank you! works like a charm 🙂

[johnt75 · Accepted solution]

Try

Wage =
SUMX (
    'Table',
    VAR currentEmployee = 'Table'[Employee ID]
    VAR summaryTable =
        TOPN (
            1,
            FILTER ( 'Table', 'Table'[Employee ID] = currentEmployee ),
            'Table'[Workagreement.Id]
        )
    RETURN
        SUMX ( summaryTable, 'Table'[Monthly wage] )
)

[henkka]

Hi @johnt75  actually when I looked the numbers more carefully and there is some issue still with the measure. Everything else is ok, but it sums wages twise if there is duplicate employee.id. For example the first employee from the table has the latest wage of 3 600 and the measure returns 7 200 (3 600*2). All good when there is only one wage.
 

[johnt75]

That employee has 2 records with the same work agreement ID, you may need to use the wage.id column to disambiguate. The below code will use the highest wage.id in the event of ties.

Wage =
SUMX (
    'Table',
    VAR currentEmployee = 'Table'[Employee ID]
    VAR summaryTable =
        TOPN (
            1,
            FILTER ( 'Table', 'Table'[Employee ID] = currentEmployee ),
            'Table'[Workagreement.Id], DESC,
            'Table'[wage.id], DESC
        )
    RETURN
        SUMX ( summaryTable, 'Table'[Monthly wage] )
)