SUMMARIZE BY TWO COLUMN THAN BRING OTHER RELATED VALUES TO THE ROWS

[VictorWentz]

Hello, first of all, happy new year! 

I need help with a problem that I can't solve. I am trying to summarize the data by two columns, and get the maximum date, but I want to bring the value from the status column referring to this maximum date.

Here is an example:

My data:

Person	training	validationDate	Status
p1	nr10	31/08/2022	red
p1	nr10	01/09/2022	blue
p2	nr12	19/09/2022	green
p2	nr10	31/08/2022	red
p3	nr11	20/09/2022	red
p4	nr10	21/09/2022	green
p1	nr10	02/09/2022	green
p2	nr12	31/12/2022	green
p3	nr11	12/03/2022	blue
p2	nr12	01/01/2023	blue

With

SUMMARIZE(myData, myData[Person], myData[training], "maxDate", MAX(myData[validationDate]))

I get:

Person	training	maxDate
p1	nr10	02/09/2022
p2	nr10	31/08/2022
p2	nr12	01/01/2023
p3	nr11	20/09/2022
p4	nr10	21/09/2022

So far so good. but what's the next step? 

The result I desire is:

Person	training	maxDate	Status
p1	nr10	02/09/2022	green
p2	nr10	31/08/2022	red
p2	nr12	01/01/2023	blue
p3	nr11	20/09/2022	red
p4	nr10	21/09/2022	green

I tried the following code:

SUMMARIZE(myData, myData[Person], myData[training], "maxDate", MAX(myData[validationDate]), "Status", SELECTEDVALUE(myData[Status]))

but the code just get the status where the summarize has a single value. I think it's a way to get the status value ordering by date, and getting the last value but I got nowhere.

Thanks for your time in helping me. Have a nice day!

[jgeddes · Accepted solution]

You can try...

SUMMARIZE(
    myData,
    myData[Person],
    myData[training],
    "maxDate", MAX(myData[validationDate]),
    "status", LOOKUPVALUE(myData[Status], myData[Person], [Person], myData[training], [training], myData[validationDate], MAX(myData[validationDate]))
)

[VictorWentz]

Hi @jgeddes ,

It worked!

Thanks for your help, I really appreciate it 🙂