Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Thanks - works well.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |