Forum Discussion

Turduckin's avatar
Turduckin
Frequent Visitor
4 years ago
Solved

Filter Context within variable

Hi all, I'm working on a DAX measure that will provide the count of students who enrolled in classes during their first fall and winter quarters. PBIX File here: https://drive.google.com/file/d/1S...
  • milanpasschier2's avatar
    milanpasschier2
    4 years ago

    Hey Turduckin ,

    Create a calculated column FallAndWinter:

     

     

    FallAndWinter = 

VAR CurrentTypeQuarterName = StudentPersistance[Quarter Name]

VAR CurrentStudentYearTable =
    CALCULATETABLE (
        StudentPersistance,
        ALLEXCEPT ( StudentPersistance, StudentPersistance[Student ID], StudentPersistance[Year] )
    )

VAR HasWinter = FILTER(CurrentStudentYearTable, StudentPersistance[Quarter Name] = "Winter")

RETURN

IF (CurrentTypeQuarterName = "Fall",
    IF (
        NOT(ISBLANK(COUNTROWS(HasWinter))), 1
    )
)

     

     

    With this you can create a measure to calculate how many of the students are in Fall and Winter in the their first year:

     

     

    Students in Fall And Winter First Year = 

VAR CurrentYear = MAX(StudentPersistance[Year])

VAR StudentTable =
    CALCULATETABLE (
        StudentPersistance,
        ALLEXCEPT ( StudentPersistance, StudentPersistance[Student ID] )
    )

VAR FirstFallAndWinter = MINX(FILTER(StudentTable, StudentPersistance[FallAndWinter] = 1), StudentPersistance[Year])

RETURN

IF (CurrentYear == FirstFallAndWinter, 1)

     

     

     

    Total Students in Fall and Winter First Year = SUMX(VALUES(StudentPersistance[Student ID]), _measures[Students in Fall And Winter First Year])

     

     

    You can download the pbix-file here:

     

    https://milanpasschier2.s3.eu-central-1.amazonaws.com/Com+3.pbix

     

    Best,

     

    Milan

Turduckin's avatar
Turduckin
Frequent Visitor
4 years ago

So, I looked more in depth at this and, I'm not sure 😔

It looks like that whole equation isn't doing what I want, other than providing numbers split between years (but the numbers are incorrect).

Turduckin's avatar
Turduckin
Frequent Visitor
4 years ago

So, on some further investigation using SQL to get the numbers I'm after, this is about what the numbers should look like and in the format I'd expect.

 

 

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Turduckin ,

     

    Goal: "look at records that have the lowest Sequence ID for each Student ID",

    get the first Sequence ID of each Student ID:

    MIN(StudentPersistance[Sequence ID])

     

    But, "that have a sequential "Fall" [Year] -> "Winter" [Year]."

    Can you explain what does it mean or how to calculate that by the following example.

     

    Best Regards

    Community Support Team _ chenwu zhu

    • Turduckin's avatar
      Turduckin
      Frequent Visitor
      4 years ago

      Hi,

      from the screenshot, the expected outcome would be 1 as only student id 11003483 has a Fall and Winter quarter enrollment in their first year.

      The Sequence ID may be spurious when what I'm looking for is minimum year. Which, I can get the minimum year when the emplid is provided in a table visualization, but I get confused on how to get that Student ID's minimum year in the variable and have it return that person's min year and not the overall min year.

  • milanpasschier2's avatar
    milanpasschier2
    Helper I
    4 years ago

    Hi, can you post the SQL to have a look?

    • milanpasschier2's avatar
      milanpasschier2
      Helper I
      4 years ago

      I don't really understand the question, what is it you would like to calculate? How many of the students are in Fall and Winter in the same year? You could try:

       

      A = 


VAR _FALL = MAXX(CALCULATETABLE(StudentPersistance, FILTER(StudentPersistance, StudentPersistance[Quarter Name]="Fall")), StudentPersistance[Quarter Name])
VAR _WINTER = MAXX(CALCULATETABLE(StudentPersistance, FILTER(StudentPersistance, StudentPersistance[Quarter Name]="Winter")), StudentPersistance[Quarter Name])

RETURN

IF(_FALL = "Fall", 
IF(_WINTER = "Winter", 1
))

       

      B = SUMX(VALUES(StudentPersistance[Student ID]), _measures[A])

       

      • Turduckin's avatar
        Turduckin
        Frequent Visitor
        4 years ago

        Hey thanks!

        I'm trying to grok the DAX you've written, but I'm kinda slow at it. In the meantime, the clarifcation statement that you have is almost right: > How many of the students are in Fall and Winter in the same their first year.

        T        he SQL that got me closer to the answer looks something like this (sorry if the fields don't match 1:1 with the pbix file, I've been renaming for legibility):

         

        With DegreeSeekingStudents As(
        SELECT
        StudentID
        FROM fact_Enrollment
        Where
        DegreeLevel in ('Undergrad','Graduate','Doctoral')
        AND Group Not In ('ContinuingEd', 'NoCredit')
        ),

        CTE As(
        Select
        *,
        MIN(AcademicYear) Over (Partition by StudentID) As FirstAY
        From StudentPersistance
        Where StudentID in (Select Distinct StudentID From DegreeSeekingStudents)
        ),

        CTE2 As(
        Select
        StudentID,
        Term,
        AcademicYear,
        FirstAY
        From CTE
        Where
        AcademicYear = FirstAY
        And ( RIGHT(Term, 1) = 2 )
        ),

        CTE3 As(
        Select
        StudentID,
        Term,
        AcademicYear,
        FirstAY
        From CTE
        Where
        AcademicYear = FirstAY
        And ( RIGHT(Term, 1) = 3 )
        ),

        CTE4 As(
        Select
        CTE2.StudentID,
        CTE2.Term As FallTerm,
        CTE3.Term As WinterTerm,
        CTE2.FirstAY
        From CTE2
        Join CTE3 On CTE2.StudentID = CTE3.StudentID
        )

        Select FirstAY, Count(*) As FallToWinter
        From CTE4
        Group by FirstAY