How to count number of records based on a value in a measure

Simon_Evans — Wed, 29 Mar 2023 14:02:15 GMT

Hi All

I'm a bit stuck with what is rather a simple requirement.

What I need to do is to end up displaying a single figure on a card visual which is the count of the number of students where there percentage attendance is less than 0.90 (90%). The card visual will be affected by a date slicer. The slicer uses the date field in the SessionAttendance table.

Here are my two tables and their relationship

The relationship is joined on the Student ID that exists in both tables.

This is a sample of the SessionAttendance Table

Date	Employee	Attendance Code	Student ID
15/09/2022	A546318024	A1161584171	A1002809422
30/09/2022	A546318024	A1161584171	A1002809422
22/09/2022	A546318024	A1161584171	A1002809422
23/09/2022	A546318024	A1161584171	A1002809422
29/09/2022	A546318024	A1161584171	A1002809422
12/10/2022	A546318024	A1161584171	A1002809422
21/09/2022	A546318024	A1161584171	A1002809422
18/10/2022	A546318024	A1161584171	A1002809422
02/11/2022	A546318024	A1161584171	A1002809422
08/11/2022	A546318024	A1161584171	A1002809422
27/09/2022	A546318024	A1161584171	A1002809422
05/10/2022	A546318024	A1161584171	A1002809422
13/09/2022	A546318024	A1161584171	A1002809422
17/01/2023	A546318024	A1329183376	A1002809422
18/01/2023	A546318024	A1329183376	A1002809422
19/01/2023	A546318024	A1329183376	A1002809422
20/01/2023	A546318024	A1329183376	A1002809422
23/01/2023	A546318024	A1329183376	A1002809422
24/01/2023	A546318024	A1329183376	A1002809422
21/02/2023	A546318024	A1329183376	A1002809422
22/02/2023	A546318024	A1329183376	A1002809422
23/02/2023	A546318024	A1329183376	A1002809422
24/02/2023	A546318024	A1329183376	A1002809422
27/02/2023	A546318024	A1329183376	A1002809422
28/02/2023	A546318024	A1329183376	A1002809422
21/09/2022	A546318024	A1161584171	A1016898777
04/10/2022	A546318024	A1161584171	A1016898777
08/03/2023	A546318024	A1161584171	A1016898777
31/10/2022	A546318024	A1161584171	A1016898777
14/09/2022	A546318024	A1161584171	A1016898777
20/10/2022	A546318024	A1161584171	A1016898777
16/09/2022	A546318024	A1161584171	A1016898777
21/10/2022	A546318024	A1161584171	A1016898777
19/10/2022	A546318024	A1161584171	A1016898777

This is a sample of the Students table

StudentID	Gender	DateOfBirth	YearCode	RegistrationGroup	PartTime	AdmissionDate	Ethnicity	EthnicCode
A1750145926	FEMALE	12/04/2017	1	OAK	No	02/09/2019	White - English	WENG
A1542680286	FEMALE	22/09/2016	1	OAK	No	02/09/2019	White - English	WENG
A1249189494	MALE	05/05/2017	1	ASH	No	06/09/2021	White - English	WENG
A507895988	MALE	02/05/2017	1	PINE	No	06/09/2021	White - English	WENG
A1565350494	FEMALE	13/07/2017	1	OAK	No	06/09/2021	White - English	WENG
A922796757	MALE	21/07/2017	1	PINE	No	06/09/2021	White - English	WENG
A1235433395	MALE	20/09/2017	R	PINE	No	05/09/2022	White - English	WENG
A1794198628	FEMALE	20/05/2018	R	ASH	No	05/09/2022	White - English	WENG
A1511763796	MALE	17/10/2017	R	PINE	No	05/09/2022	White - English	WENG
A1999965237	FEMALE	03/12/2017	R	ASH	No	05/09/2022	White - English	WENG
A1433408704	MALE	15/02/2017	1	ASH	No	02/09/2019	White - English	WENG
A1350601327	MALE	21/05/2017	1	ELM	No	06/09/2021	White - English	WENG
A1002809422	MALE	15/06/2015	3	3TO	No	02/09/2019	White - English	WENG
A317854122	FEMALE	20/12/2014	3	3TO	No	02/09/2019	White - English	WENG
A619309687	FEMALE	21/04/2015	3	3TO	No	02/09/2019	White - English	WENG
A1293808694	MALE	15/02/2013	5	5BB	No	01/09/2017	White - English	WENG
A392403471	MALE	07/11/2014	3	3CB	No	01/09/2017	White - English	WENG
A1234098928	MALE	12/05/2015	3	3CB	No	02/09/2019	White - English	WENG
A687256901	MALE	19/02/2015	3	3TO	No	02/09/2019	White - English	WENG
A1984655952	FEMALE	20/11/2016	1	OAK	No	03/09/2020	White - English	WENG
A90208359	FEMALE	02/01/2014	4	4SL	No	01/09/2016	White - English	WENG
A122545299	FEMALE	28/06/2018	R	OAK	No	05/09/2022	White - English	WENG
A1803153734	MALE	16/07/2017	1	ELM	No	06/09/2021	White - English	WENG
A1832738044	MALE	17/04/2015	3	3CB	No	01/09/2018	White - English	WENG
A670724717	FEMALE	03/02/2015	3	3TO	No	02/09/2019	White - English	WENG
A790338908	MALE	16/10/2017	R	OAK	No	06/09/2021	White - English	WENG
A1821313148	FEMALE	14/06/2014	4	4ES	No	01/09/2017	White - English	WENG

I have the following DAX measure on the SessionAttendance table which calculates the percentage attendance by counting the appropriate codes for present and then dividing that by the count of the possible attendances codes. The result is a decimal figure.

Pupil % Attendance = var _PossSessions = CALCULATE(COUNTROWS(SessionAttendance),SessionAttendance[Attendance Code]<>"A930902628" && SessionAttendance[Attendance Code]<>"A1801476586" && SessionAttendance[Attendance Code]<>"A1131916182" && SessionAttendance[Attendance Code]<>"A1299388415" && SessionAttendance[Attendance Code]<>"A1462380788" && SessionAttendance[Attendance Code]<>"" && SessionAttendance[Attendance Code]<>"A124965801" && SessionAttendance[Attendance Code]<>"A493975360" && SessionAttendance[Attendance Code]<>"A795820045" && SessionAttendance[Attendance Code]<>"A627303844" && SessionAttendance[Attendance Code]<>"A996587839" && SessionAttendance[Attendance Code]<>"A323305093" && SessionAttendance[Attendance Code]<>"A700043804") var _PresentMarks = CALCULATE(COUNTROWS(SessionAttendance),SessionAttendance[Attendance Code]="A1329183376" || SessionAttendance[Attendance Code]= "A1161584171" || SessionAttendance[Attendance Code]="A830070477" || SessionAttendance[Attendance Code]="A186523258" || SessionAttendance[Attendance Code]="A1529934786"|| SessionAttendance[Attendance Code]="A319327454"|| SessionAttendance[Attendance Code]="A1500139297"|| SessionAttendance[Attendance Code]="A1601102008") Return _PresentMarks / _PossSessions

When I use the above measure in a table along with the Students name I get the % attendance for each student so I know the measure works.

What I am now stuck on is how to combine this measure with a count and filter DAX expression to get the count of Students where the % attendance in the measure is less than 0.90. Can anyone assist please?

Kindest regards

Simon

Re: How to count number of records based on a value in a measure

tamerj1 — Wed, 29 Mar 2023 14:22:19 GMT

Hi Simon_Evans

please try

Count > 90% =
SUMX ( VALUES ( Students[StudentID] ), INT ( [Pupil % Attendance] > 0.9 ) )

Re: How to count number of records based on a value in a measure

Simon_Evans — Wed, 29 Mar 2023 14:47:33 GMT

tamerj1

Thank you so much. Cannot believe it was such a short DAX measure.

However it appears the formula is counting more than it should which Im struggling to work out why as I can put it alongside a table with the studentIDs and the percentage attendance.

This is what I have. For the date range of the slicer you can see that the formula comes back with a count of 7 but according to the table it should be 6.

I slightly tweaked the formula to the below.

Count Less than 90% = SUMX(VALUES(Students[WondeStudentID]),INT([Pupil % Attendance]<=0.9))

Any ideas as to why it is over counting at all please?

Re: How to count number of records based on a value in a measure

Simon_Evans — Wed, 29 Mar 2023 15:12:04 GMT

tamerj1 Thank you once again. Don't worry about why it was miss counting. I think that is something within the test dataset I am using as I have tried this on a non live version of a customers data set and it is working perfectly!

Re: How to count number of records based on a value in a measure

pavansai909 — Wed, 29 Mar 2023 17:19:44 GMT

I believe you must use the below one to get correct count:

Count Less than 90% = SUMX(VALUES(Students[WondeStudentID]),INT([Pupil % Attendance]< 0.9))

topic Re: How to count number of records based on a value in a measure in DAX Commands and Tips

How to count number of records based on a value in a measure

Re: How to count number of records based on a value in a measure

Re: How to count number of records based on a value in a measure

Re: How to count number of records based on a value in a measure

Re: How to count number of records based on a value in a measure