Previous value in a sequence

Question

I have a dataset with the folllowing data points:

unique Student ID (which student took the class)
Class ID # (what class did they take)
Sequence (what number course was this that they've taken from us 1=first course, 2=second course, 3=third course, etc.)

Student ID	Class ID	Sequence
1101	4433	1
1101	3377	2
1102	3221	1
1101	5698	3
1102	3222	2

What I want to know is if there were 100 students whose first class was Class ID 4433 (Class ID = 4433 AND Sequence = 1), what was the next class they took with us.

For example:

Total # of Students with First Class ID 4433

100

Total # of Students with Second Class ID 5589	Total # of Students with Second Class ID 55890	Total # of Students with Second Class ID 5591	Total # of Students with Second Class ID 6778
50	10	25	15

I also want to see third classes and fourth etc. For each Class ID, I'd also like to see the average number of courses those student's go on to take.

ashish_mathur · Answer

Hi,
How does one interpret the last table?&nbsp; Please elaborate with a dataset which has more number of rows.

erikriesenberg · Answer

Ashish,&nbsp;Thanks for the reply.&nbsp; I see I didn't do a very good job explaining.&nbsp;&nbsp;&nbsp;Maybe it will make more sense if I use more precise data.&nbsp;&nbsp;&nbsp;Here's a sample of my data set:Student IDSequenceClass667241AZ-300 Azure Architect Technologies671941CompTIA Cybersecurity Analyst Certification CySA+ (Exam CS0-001)671942CompTIA Cybersecurity Analyst (CySA+) Certification (Exam CS0-002)684091Excel 2016 - Part 2684092Excel 2016 - Part 369766110982 Supporting and Troubleshooting Windows 10697662VMware Horizon® 7 - Install, Configure, Manage V7.7704481Excel 2016 - Part 2704482Outlook 2016 - Part 2704483PowerPoint 2016 - Part 272545120341 Core Solutions of Microsoft Exchange Server 2013725452AWS Solutions Architect Certification - Associate725453AZ-103T00 - Microsoft Azure Administrator725454NCTA Cloud Architecture725461AWS Solutions Architect Certification - Associate725462NCTA Cloud Architecture736331Adobe Photoshop CC Part 1 (with Labs)742581Custom Course755651Office 365 Web Apps (with Skype for Business)755652Microsoft Teams755653SharePoint - Site Owner with Microsoft Forms and Flow (Modern Experience)75565455238 SharePoint Online for Administrators&nbsp;Student ID 66724 took one course and never returned to take another course.&nbsp; 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.&nbsp;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?&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;Beyond that, if Excel 2016 - Part 2 was their first class, what was their third class, fourth class, etc.&nbsp; 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.&nbsp; I want to see if there are certain classes are more likely to follow others based on students past enrollment behaviors.

anonymous · Answer

Hi&nbsp;erikriesenberg&nbsp;，
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
&nbsp;
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 &gt; 1 &amp;&amp; NOT ( ISBLANK ( _tclass ) ) )
    )Count of students who take selected class = SUMX(VALUES('Table'[Student ID]),[Tempcount])
&nbsp;

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

erikriesenberg · Answer

Anonymous&nbsp;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.&nbsp;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.&nbsp; 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.&nbsp; More importantly I want to know what class they take when they come back.&nbsp;&nbsp;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.&nbsp; 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:&nbsp;Second CourseNumber of unique students who took this as their second courseThird CourseNumber of unique students who took this as their third courseExcel 2016 - Part 31&nbsp;&nbsp;Outlook 2016 - Part 21PowerPoint 2016 - Part 21&nbsp;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.&nbsp; 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.&nbsp;&nbsp;&nbsp;A visual like this:&nbsp;This would mean that each different combination of courses would have to be it's own unique value.&nbsp; Does that make sense?

ashish_mathur · Answer

Hi,
Have you been able to solve this question?

Forum Discussion

Previous value in a sequence

7 Replies

Related content

Getting value for previous year based on value name and year slicer

Previous Day Value

Previous Row Value Dax

Previous Day Value

Previous date values

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