Creating a Custom Column to Show Instances of Primary Key

[Anonymous]

I have a number of students who have taken several classes on our educatonal platform. I have separate pages that shows the demographic breakdown of each class and another page that shows the demographic breakdown across all classes. I am trying to see if there is a way to create a column or organize my data so that I can show a breakdown of the number (and ideally names) of classes students have taken. As an example my raw data looks like this:

Student ID	Class Year	Course
S001	Freshman	Math
S002	Sophomore	Math
S003	Junior	Math
S004	Senior	Math
S003	Junior	Physics
S004	Senior	Physics
S005	Senior	Physics
S001	Freshman	Chemistry
S002	Sophomore	Chemistry
S005	Senior	Physics
S006	Junior	Chemistry
S007	Junior	Chemistry
S004	Senior	Economics
S008	Junior	Economics
S009	Junior	Economics
S010	Sophomore	Economics

The one idea I had was to do a count that would look something like this:

Student ID	Number of Courses
S001	2
S002	2
S003	2
S004	3
S005	2
S006	1
S007	1
S008	1
S009	1
S010	1

But aside from not knowing how to do that in DAX (question #1!) I would ideally want to use this "number of classes" into a slicer--or perhaps add relationship for when people clicked on a visual--that would show how the breakdown of Fresh/Soph/Junior/Senior changed for students that took, for example, 3 classes (and which 3 they were).

Thank you in advance

[m_dekorte · Accepted solution]

Hi @Anonymous,

For a slicer you need a column with the Class count in your table.

You can achieve this in Power Query, leveraging the Group By transformation. After the count has been added you can bring back the other fields by using the expand column option, the sideward arrows in the column header.

Or a calculated column

With a simple rowcount, and depending on what you bring into your visual you'll see a break down of that number by the selected attributes. For example:

Ps. If this helps solve your query please mark this post as Solution, thanks!