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 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
Solved! Go to 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!
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!
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.