Forum Discussion
Previous value in a sequence
Ashish,
Thanks for the reply. I see I didn't do a very good job explaining.
Maybe it will make more sense if I use more precise data.
Here's a sample of my data set:
| Student ID | Sequence | Class |
| 66724 | 1 | AZ-300 Azure Architect Technologies |
| 67194 | 1 | CompTIA Cybersecurity Analyst Certification CySA+ (Exam CS0-001) |
| 67194 | 2 | CompTIA Cybersecurity Analyst (CySA+) Certification (Exam CS0-002) |
| 68409 | 1 | Excel 2016 - Part 2 |
| 68409 | 2 | Excel 2016 - Part 3 |
| 69766 | 1 | 10982 Supporting and Troubleshooting Windows 10 |
| 69766 | 2 | VMware Horizon® 7 - Install, Configure, Manage V7.7 |
| 70448 | 1 | Excel 2016 - Part 2 |
| 70448 | 2 | Outlook 2016 - Part 2 |
| 70448 | 3 | PowerPoint 2016 - Part 2 |
| 72545 | 1 | 20341 Core Solutions of Microsoft Exchange Server 2013 |
| 72545 | 2 | AWS Solutions Architect Certification - Associate |
| 72545 | 3 | AZ-103T00 - Microsoft Azure Administrator |
| 72545 | 4 | NCTA Cloud Architecture |
| 72546 | 1 | AWS Solutions Architect Certification - Associate |
| 72546 | 2 | NCTA Cloud Architecture |
| 73633 | 1 | Adobe Photoshop CC Part 1 (with Labs) |
| 74258 | 1 | Custom Course |
| 75565 | 1 | Office 365 Web Apps (with Skype for Business) |
| 75565 | 2 | Microsoft Teams |
| 75565 | 3 | SharePoint - Site Owner with Microsoft Forms and Flow (Modern Experience) |
| 75565 | 4 | 55238 SharePoint Online for Administrators |
Student ID 66724 took one course and never returned to take another course. Student ID 68409 took Excel 2016 - Part 2 and then came back for Part 3 whereas Student ID 70448 also started with Excel 2016 - Part 2 first, but then took Outlook, and then Powerpoint. Both Student started with the same class, but took different subsequent classes.
What I want to be able to see is, for all of the students who took Excel 2016 - Part 2 as their first course:
1. How many took a second course?
2. What was the next course they took?
So if I have a total of 100 people that took Excel 2016 - Part 2 as their first course, how many went on to take Excel 2016 - Part 3, how many went on to take Outlook 2016, and so on.
Beyond that, if Excel 2016 - Part 2 was their first class, what was their third class, fourth class, etc. You can see that I have this data and can look at this sequence at the Student ID level, but I want to look at it at the class level. I want to see if there are certain classes are more likely to follow others based on students past enrollment behaviors.
Hi erikriesenberg ，
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a class dimension table and use the class field of the table as slicer
2. Create the measures as below to get the count of students which filter the conditions
Tempcount =
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Class] ),
ALLEXCEPT ( 'Table', 'Table'[Student ID] )
)
VAR _tclass =
CALCULATE (
MAX ( 'Table'[Class] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Student ID] ),
'Table'[Class] = SELECTEDVALUE ( 'Classes'[Class] )
)
)
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Student ID] ),
FILTER ( 'Table', _count > 1 && NOT ( ISBLANK ( _tclass ) ) )
)Count of students who take selected class = SUMX(VALUES('Table'[Student ID]),[Tempcount])
If the above one is not your expected result, please provide more details(calculation logic, special examples and screenshot etc.) for it. Thank you.
Best Regards
Anonymous Thanks for the work on this, but this isn't quite what I was looking for though I like your idea of creating a Class dimension table.
Your example shows that selected Excel 2016 - Part 2 in slicer, but it looks like your calcuations just tells me how many people took Excel 2016 - Part 2 which I already know. I want to know how many of those Excel 2016 - Part 2 students who took this as their FIRST class came back for a SECOND class. More importantly I want to know what class they take when they come back.
My ideal outcome is that if I were to click on Excel 2016 - Part 2 in a slicer, that I get a list of all the courses that people took as their second course and how many students took that as their second course. Using your sample file as an example, if I selected Excel 2016 - Part 2 in the slicer, this is what I'd want to see:
Second Course Number of unique students who took this as their second course Third Course Number of unique students who took this as their third course Excel 2016 - Part 3 1 Outlook 2016 - Part 2 1 PowerPoint 2016 - Part 2 1 What would be even better than a table chart, would be a ribbon chart that shows the course path of all the subsequent courses a student takes based on a selected starting point. So if I were to pick Excel 2016 - Part 2 as a starting point, we may have thousands of students that take that as their first class.
A visual like this:
This would mean that each different combination of courses would have to be it's own unique value. Does that make sense?