cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Counting 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.tbruh.PNG
t_Calendar.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Counting ELA and MATH

@syasmin25 

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 

SU18_powerbi_badge

 

View solution in original post

12 REPLIES 12
Highlighted
Solution Specialist
Solution Specialist

Re: Counting ELA and MATH

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:
students.png

 

Power BI file is available here.

 

Please accept as solution if this is what you are looking for.

 

Kind regards,

 

Steve. 

Highlighted
Super User III
Super User III

Re: Counting ELA and MATH

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
    )
) + 0

 

 

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 

SU18_powerbi_badge

Highlighted
Super User III
Super User III

Re: Counting ELA and MATH

@syasmin25 

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 

SU18_powerbi_badge

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Counting ELA and MATH

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

Highlighted
Helper IV
Helper IV

Re: Counting ELA and MATH

I would need a distinct count on the IDs.

Highlighted
Helper IV
Helper IV

Re: Counting ELA and MATH

It 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.

Highlighted
Solution Specialist
Solution Specialist

Re: Counting ELA and MATH

In my response I share a power bi file with a working solution?

Highlighted
Helper IV
Helper IV

Re: Counting ELA and MATH

Yes, I am a bit confused since I will also be providing a drillthrough. When I did that to the file that you have provided, it shows other subjects too when I copy in the formula and do a drill through with the IDs and periods and subjects.

Highlighted
Solution Specialist
Solution Specialist

Re: Counting ELA and MATH

Perhaps I can help if you can share that file? Perhaps via a PM?

 

Kind regards,

 

 

Steve. 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021