Count of students in a subject

[dphillips]

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.

[v-yulgu-msft · Accepted solution]

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

[dphillips]

Thanks - works well.

[Greg_Deckler]

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

[dphillips]

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.