Forum Discussion
Creating a Custom Column to Show Instances of Primary Key
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
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!
1 Reply
- m_dekorte
Resident Rockstar
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!