Forum Discussion
Max Record By ID at Slicer End Date - Summary Count Cards Not Working
Community ChampionI see your point, and it appears you want to count the number of students with a specific status ("Sick" or "Work Placement") at the slicer end date, considering only the most recent status for each student.
To achieve this, you can indeed use DAX measures. You can create separate measures for each status, and for each measure, filter the student_attendance_data table to consider only the most recent status records per student that match the desired status and fall on or before the slicer end date. Here's how you can do it for "Sick" and "Work Placement":
For "Sick" Status:
Measure Sick = VAR SlicerEndDate = MAX('DataTable'[Date]) RETURN CALCULATE( COUNTROWS(student_attendance_data), FILTER( ALLEXCEPT(student_attendance_data, student_attendance_data[StudentID]), student_attendance_data[Status] = "Sick" && student_attendance_data[Status_start_date] <= SlicerEndDate ) )
For "Work Placement" Status:
Measure WorkPlacement = VAR SlicerEndDate = MAX('DataTable'[Date]) RETURN CALCULATE( COUNTROWS(student_attendance_data), FILTER( ALLEXCEPT(student_attendance_data, student_attendance_data[StudentID]), student_attendance_data[Status] = "Work Placement" && student_attendance_data[Status_start_date] <= SlicerEndDate ) )
These measures filter the student_attendance_data table to consider only the rows for the specified status ("Sick" or "Work Placement") and where the Status_start_date is on or before the slicer end date. They also use ALLEXCEPT to remove any other filters except the StudentID to ensure that only one record per student is counted.
Make sure to replace 'DataTable', 'Date', 'student_attendance_data', 'StudentID', 'Status', and 'Status_start_date' with the actual names of your tables and columns.
With these measures, you should be able to count the number of students with the specified statuses at the slicer end date, considering only the most recent status record for each student.
Hi
The Problem with the below measure
Measure Sick =
VAR SlicerEndDate = MAX('DataTable'[Date]) RETURN CALCULATE( COUNTROWS(student_attendance_data), FILTER( ALLEXCEPT(student_attendance_data, student_attendance_data[StudentID]), student_attendance_data[Status] = "Sick" && student_attendance_data[Status_start_date] <= SlicerEndDate ) )
Is that it counts all status "Sick" even if it's not the most recent Status at Student Level(at the slicer end date).
What I need is for the counts to only count 1 record per student across all Statuses.
Therefore across the 4 measures (New, Left, Sick, Work Placement Status), there are 3 Students, therefore the sum total for all 3 Status Measures should be 3 (as 3 Students).
- Sick should be = 2 (J Smith Max Record closest prior to Slicer max 8/5/23 started 5/5/23, Lee Taylor 5/5/23)
- Work Placement = 1 (D Jones Max Record closest prior to slicer max, started 3/3/23).
Therefore the above DAX, would also need to check if the attendance record at Student Level is the Max Prior to the Slicer Max Date, if it's not it should not be counted in the measure.
If it's not possible in Measures happy to have a dynamic table or matrix, but it needs to reculate when the slicer changes and just show a summary view, i.e. the Status and Count of Students.
Is this possible?
Thanks