Custom measure to show how many times student appears in list based on filter and slicer

[newtonian1991]

Hi All, 

have had several atytempts at this and cant get it working wiht sum, sumx and count row functions.

I have the following information and would like a custom measure to calculate the total times a student shows up in the table where title = "Late Arrival" this data gets filtered by a between date slicer. I then need this value to appear next to every occurance in the list. In this exampel it would be a total count of 2 showing up on each line. 

As well as that i would then like to create display a tabel that shows the number of students with 0-2 lates 3-5 lates 6-8 lates etc. 

Title	StudentID	Year Group	Form	Date/Time	Reason	Early Departure Count
Late Arrival	134336	Year 11	11MOO	16/5/2023 14:26	Senior Pass Tag Out	1
Late Arrival	134336	Year 11	11MOO	13/6/2023 14:27	Senior Pass Tag Out	1

Any help would be greatly appreciated. 

[newtonian1991]

almost go there with the below dax but the column value doesnt change when i change the slicer. 

Late Arrivals Count = 
VAR SelectedStartDate = MIN('Late Arrival And Early Departure'[LocalTimeCombined])  // Replace with the actual slicer field
VAR SelectedEndDate = MAX('Late Arrival And Early Departure'[LocalTimeCombined])      // Replace with the actual slicer field
RETURN
    CALCULATE(
        COUNTROWS('Late Arrival And Early Departure'),
        FILTER(
            'Late Arrival And Early Departure',
            'Late Arrival And Early Departure'[PupilID] = EARLIER('Late Arrival And Early Departure'[PupilID]) &&
            'Late Arrival And Early Departure'[Title] = "Late Arrival" &&
            'Late Arrival And Early Departure'[CheckInLocalTime] >= SelectedStartDate &&
            'Late Arrival And Early Departure'[CheckInLocalTime] <= SelectedEndDate
        )
    )

[Ashish_Mathur]

Hi,

Share a dataset with more rows.  What is the use of the time stamp in the Date/Time column?  Also, would you be OK with a measure only solution (no calculated column)?

[newtonian1991]

Yes, anythign that gets me the result really.

as im a new user it seems as though i cnat attach data. please see below. 

Title	StudentID	Year Group	Form	Date/Time	Reason	Count
Early Departure	3309504	Year 11	11MOO	6/6/2023 14:23	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	18/7/2023 14:23	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	1/8/2023 14:27	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	15/8/2023 14:30	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	12/9/2023 14:59	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	15/9/2023 14:26	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	10/10/2023 13:26	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	13/10/2023 13:24	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	24/10/2023 13:46	Senior Pass Tag Out	1
Early Departure	3309504	Year 11	11MOO	6/11/2023 13:24	Senior Pass Tag Out	1
Late Arrival	3309504	Year 11	11MOO	11/7/2023 11:16	Other Reasons	1
Late Arrival	3309504	Year 11	11MOO	20/10/2023 10:01	Senior Pass Tag In	1
Late Arrival	3309504	Year 11	11MOO	3/11/2023 9:56	Senior Pass Tag In	1
Early Departure	333302833	Year 11	11LEA	19/6/2023 14:33	Medical Appointment	1
Early Departure	333302833	Year 11	11LEA	20/7/2023 14:29	Medical Appointment	1
Early Departure	333302833	Year 11	11LEA	7/8/2023 14:29	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	18/8/2023 14:22	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	15/9/2023 14:29	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	13/10/2023 14:04	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	16/10/2023 13:35	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	19/10/2023 8:51	Medical Appointment	1
Early Departure	333302833	Year 11	11LEA	24/10/2023 14:34	Medical Appointment	1
Early Departure	333302833	Year 11	11LEA	27/10/2023 13:28	Senior Pass Tag Out	1
Early Departure	333302833	Year 11	11LEA	6/11/2023 13:12	Medical Appointment	1
Late Arrival	333302833	Year 11	11LEA	1/8/2023 10:08	Senior Pass Tag In	1
Late Arrival	333302833	Year 11	11LEA	15/8/2023 9:59	Senior Pass Tag In	1
Late Arrival	333302833	Year 11	11LEA	10/10/2023 8:16	Senior Pass Tag In	1
Late Arrival	333302833	Year 11	11LEA	19/10/2023 9:45	Medical Appointment	1
Late Arrival	333302833	Year 11	11LEA	3/11/2023 9:43	Senior Pass Tag In	1
Late Arrival	333302833	Year 11	11LEA	6/11/2023 8:51	Senior Pass Tag In	1
Late Arrival	333302833	Year 11	11LEA	6/11/2023 14:06	Medical Appointment	1

[Ashish_Mathur]

Hi,

PBI file attached.

[newtonian1991]

Thank you, almost there. I was hoping the circled column in red would show the total number of lates rather thna just 1 when placed in a column so i can sort students with most lates. 

i woudl also liek to eb able to filter or cross filter by the categories you ahve created. for e.g circled in green, select 3-5 lates and table updates only to show me students with 3-5 lates. 

Thansk again for your help. 

[Ashish_Mathur]

You are welcome.  This file should help.