cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syasmin25
Helper V
Helper V

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
AlB
Super User
Super User

@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
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


The equation still carries some duplicates.

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?

I would need a distinct count on the IDs.

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

 

Power BI file is available here.

 

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

 

Kind regards,

 

Steve. 

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.

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

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.

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

 

Kind regards,

 

 

Steve. 

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

I have tried your equationn, however it almost there yet not there with the result. Like my result for one of the schools is supposed to be 52 but witht he equation, it is still showing 60. I am guessing that it is carrying in duplicate values.

AlB
Super User
Super User

@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

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors