topic Re: Counting ELA and MATH in DAX Commands and Tips

Counting ELA and MATH

syasmin25 — Fri, 29 May 2020 22:15:47 GMT

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.

Re: Counting ELA and MATH

stevedep — Sat, 30 May 2020 07:07:29 GMT

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.

Re: Counting ELA and MATH

AlB — Sat, 30 May 2020 12:30:34 GMT

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

Re: Counting ELA and MATH

AlB — Sat, 30 May 2020 12:32:38 GMT

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

Re: Counting ELA and MATH

mahoneypat — Sat, 30 May 2020 23:53:34 GMT

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

Re: Counting ELA and MATH

syasmin25 — Mon, 01 Jun 2020 16:02:18 GMT

I would need a distinct count on the IDs.

Re: Counting ELA and MATH

syasmin25 — Mon, 01 Jun 2020 16:15:43 GMT

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.

Re: Counting ELA and MATH

stevedep — Mon, 01 Jun 2020 16:46:24 GMT

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

Re: Counting ELA and MATH

syasmin25 — Mon, 01 Jun 2020 17:06:04 GMT

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.

Re: Counting ELA and MATH

stevedep — Mon, 01 Jun 2020 17:35:39 GMT

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

Kind regards,

Steve.

Re: Counting ELA and MATH

syasmin25 — Tue, 02 Jun 2020 02:48:26 GMT

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.

Re: Counting ELA and MATH

syasmin25 — Tue, 02 Jun 2020 02:50:01 GMT

The equation still carries some duplicates.

Re: Counting ELA and MATH

stevedep — Tue, 02 Jun 2020 05:30:48 GMT

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?