Forum Discussion

erikriesenberg's avatar
erikriesenberg
Frequent Visitor
5 years ago

Previous value in a sequence

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 f...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

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

  • erikriesenberg's avatar
    erikriesenberg
    Frequent Visitor
    5 years ago

    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 IDSequenceClass
    667241AZ-300 Azure Architect Technologies
    671941CompTIA Cybersecurity Analyst Certification CySA+ (Exam CS0-001)
    671942CompTIA Cybersecurity Analyst (CySA+) Certification (Exam CS0-002)
    684091Excel 2016 - Part 2
    684092Excel 2016 - Part 3
    69766110982 Supporting and Troubleshooting Windows 10
    697662VMware Horizon® 7 - Install, Configure, Manage V7.7
    704481Excel 2016 - Part 2
    704482Outlook 2016 - Part 2
    704483PowerPoint 2016 - Part 2
    72545120341 Core Solutions of Microsoft Exchange Server 2013
    725452AWS Solutions Architect Certification - Associate
    725453AZ-103T00 - Microsoft Azure Administrator
    725454NCTA Cloud Architecture
    725461AWS Solutions Architect Certification - Associate
    725462NCTA Cloud Architecture
    736331Adobe Photoshop CC Part 1 (with Labs)
    742581Custom Course
    755651Office 365 Web Apps (with Skype for Business)
    755652Microsoft Teams
    755653SharePoint - Site Owner with Microsoft Forms and Flow (Modern Experience)
    75565455238 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • erikriesenberg's avatar
        erikriesenberg
        Frequent Visitor
        5 years ago

        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 CourseNumber of unique students who took this as their second courseThird CourseNumber of unique students who took this as their third course
        Excel 2016 - Part 31  
        Outlook 2016 - Part 21PowerPoint 2016 - Part 21

         

        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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      Have you been able to solve this question?

      • erikriesenberg's avatar
        erikriesenberg
        Frequent Visitor
        5 years ago

        Not as of yet, but I'm still working on it.