Forum Discussion
Helper VCounting ELA and MATH
I have an issue and am having trouble wrapping my head around it. I am trying to count students absent in English and Math classes. The issue here is the database places students absent per period on a row to row basis. I have created a sample here. The original one is over a million rows.
I was wondering if there is any way I can create a column that counts number of students absent in MATH and ELA. I know I can do table. But I need to be looking more like the picture below (red font is where I want the calculation) and was wondering if someone could help.
or this version might be faster
Measure V2 = VAR tab0_ = FILTER ( Table1, Table1[Subject] IN { "MATH", "ELA" } ) RETURN SUMX ( tab0_, ( COUNTROWS ( FILTER ( tab0_, [ID] = EARLIER ( [ID] ) ) ) = 2 ) * ( 1 / 2 ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
12 Replies
- stevedep
Memorable Member
Hi,
This should work:
NumberOfStudentsAbsentInELAAndMath = var filterset = FILTER(PresenceStudents;PresenceStudents[Subject]="ELA" || PresenceStudents[Subject]="MATH") var summaryset_ineiterclass = SUMMARIZE(filterset;PresenceStudents[ID]) var allstudents = SUMMARIZE(PresenceStudents;PresenceStudents[ID]) return COUNTX(EXCEPT(allstudents;summaryset_ineiterclass);1)As can be seen here:
Power BI file is available here.
Please accept as solution if this is what you are looking for.
Kind regards,
Steve.
- AlB
Community Champion
Hi syasmin25
1. Place Table1[School ID] in a table visual
2. Place this measure in the visual
Measure V1 = COUNTROWS ( FILTER ( DISTINCT ( Table1[ID] ), CALCULATE ( COUNT ( Table1[ID] ), Table1[Subject] IN { "ELA", "MATH" } ) = 2 ) ) + 0Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- AlB
or this version might be faster
Measure V2 = VAR tab0_ = FILTER ( Table1, Table1[Subject] IN { "MATH", "ELA" } ) RETURN SUMX ( tab0_, ( COUNTROWS ( FILTER ( tab0_, [ID] = EARLIER ( [ID] ) ) ) = 2 ) * ( 1 / 2 ) )Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
syasmin25It is not doing a distinct count on the ID, is there a way that would be possible? I tried to go along in a few ways but it doesnt seem to work.
- stevedep
In my response I share a power bi file with a working solution?
- mahoneypat
Microsoft Employee
Not sure what happened but this same post was also listed under "Counting Core Courses". I had replied with this expression.
Absent ELA and Math =
COUNTROWS (
FILTER (
VALUES ( Absences[ID] ),
CALCULATE ( COUNTROWS ( Absences ), Absences[Subject] IN { "ELA", "Math" } ) = 2
)
)If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- syasmin25
I would need a distinct count on the IDs.
- syasmin25
The equation still carries some duplicates.
- stevedep
Sorry, I can't help further unless you provide more information. Perhaps I can when you share a Pbi file with an anonymous subset of your data that shows the problem?
