How to find latest agreement by employee and create a table

[henkka]

Hi, I have a Workagreement table, which includes Employee.id and Workagreement.id. I need to create a calculated table for the latest workagreement (biggest Workagreement.id) by employee but not able to find a solution. Please maybe someone here can help me.

My measure:

Newest agreement by employee =

-- get all employees to var --

VAR allagreement =

VALUES(FactWorkagreement[Employee.id])

--get all workagreement ids --

var agreementids = VALUES(FactWorkagreement[Workagreement.id])

--Get the latest agreement for the employee. Latestagreement = MAX workagreementid, All latest agreement = CALCULATE ( [LatestAgreement], ALL ( 'FactWorkagreement'[Workagreement.id] ) )

VAR result1 =

CALCULATE (

    COUNTX ( FactWorkagreement , FactWorkagreement[Workagreement.id] ),

    FILTER (agreementids, [LatestAgreement] = [All latest agreement] )

        )

--find the latest agreement by employee

VAR result =

FILTER ( allagreement , result1 )  

RETURN

CALCULATETABLE (

        Summarize ( FactWorkagreement ,

                    FactWorkagreement[Employee.id] , FactWorkagreement[Workagreement.id] ) , result

               )

With this I'm getting a table, but it includes all of the workagreements, not only the latest by employee.

[v-jialluo-msft · Accepted solution]

^{Hi @henkka ,}

^{Please follow these steps:}

^{(1) Create new table and measure}

Max = CALCULATE(MAX('Table'[Workagreement.id]),FILTER(ALL('Table'),'Table'[Employee.id]= MAX('Table'[Employee.id])))

 

Table 2 = FILTER(ALL('Table'),'Table'[Workagreement.id]=[Max])

 

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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

[henkka]

Thank you very much, modified this a bit as I don't need other columns from the table, but works like a charm!

If someone seeks this and has similar need  that I have, modify the second part of the dax as this:

FILTER (

    ALLSELECTED ( 'FactWorkagreement'[Employee.id] , FactWorkagreement[Workagreement.id] ) ,

    'FactWorkagreement'[Workagreement.id]=[Max]

    )