cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dphillips Regular Visitor
Regular Visitor

Count of students in a subject

I have a table of students in different subjects (Assessment Code). How do I get the total number of students in each Assessment Code for each Fileyear and Filesemester?

 

If I was doing this in TSQL I could do

 

Count(R.Id) over (partition by R.Fileyear,R.FileSemester,R.Assessmentcode) as StudCount,

 

How do I do this in PowerBI in DAX? Do I use a measure or calculated column? I am assuming a calculated column but not sure how to go about partitioning the count over the Fileyear, Filesemester and AssessmentCode.

 

Any help appreciated.

 

Count of Students.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Count of students in a subject

Hi @dphillips,

 

You can create a calculated column similar to below:

Count student =
CALCULATE (
    COUNT ( table[ID] ),
    ALLEXCEPT ( table, table[Assessmentcode], table[Fileyear], table[FileSemester] )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Count of students in a subject

Typicall you would just create a table or matrix visualization with your Assessment Code, Fileyear and Filesemester in it and a Count of some other field. 

 

Sample data that can be copied and pasted is always more helpful than screen shots. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dphillips Regular Visitor
Regular Visitor

Re: Count of students in a subject

Thanks for the heads up regarding data. I have a pbix file to show you but not sure how I can attach this. Can you let me know how as I can't see anywhere to attache the data.

 

I can easily get a count of the students but my problem occurs when I use a slicer to select just one student to see his results. By filtering the data, the number of students in each subject is, obviously 1.  How do I get the total number of students in each subject even when I have selected only 1 student in a slicer?

 

Happy to send the data if you could help on the best way of doing this. Thanks for your help.

Community Support Team
Community Support Team

Re: Count of students in a subject

Hi @dphillips,

 

You can create a calculated column similar to below:

Count student =
CALCULATE (
    COUNT ( table[ID] ),
    ALLEXCEPT ( table, table[Assessmentcode], table[Fileyear], table[FileSemester] )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dphillips Regular Visitor
Regular Visitor

Re: Count of students in a subject

Thanks - works well.