Column value based on Latest date and category filter

santhiya — Mon, 13 Mar 2023 07:43:45 GMT

Hi @Community!!!
I am having table "Student" as below

id name

1 sam

2 kind

and "Student Activity" as below

student_id status date reported_to

1 started 12/03/2023 bingston

1 break 14/03/2023 rithu

1 started 15/03/2023 rithivi

1 finished 17/03/2023 kiwn

2 started 19/04/2023 ram

2 break 20/04/2023 dithirn

2 started 21/04/2023 rahul
2 break 24/04/2023 rahul
2 finished 25/04/2023 nivin

Student table has relationship with Student activity with id and student_id respectively

I need to find the "reported to "column value for each student for the latest date in status with started or break as status and add it as a calculated column(latest_reported_person) to Student Table

for example student id 1 has latest date 15/03/2023 in started and the reported_to column value is rithivi
similarly student id 2 has latest date 24/03/2023 in break status and the reported_to column value is rahul

How to achieve this with DAX?

Re: Column value based on Latest date and category filter

Greg_Deckler — Mon, 13 Mar 2023 07:52:31 GMT

santhiya Try:

Column = VAR __MaxDate = MAXX(FILTER(RELATEDTABLE('Activity'),[status]="started"),[date]) VAR __ReportedTo = MAXX(FILTER(RELATEDTABLE('Activity'),[status]="started" && [date] = __MaxDate),[reported_to) RETURN __ReportedTo

topic Column value based on Latest date and category filter in DAX Commands and Tips

Column value based on Latest date and category filter

Re: Column value based on Latest date and category filter