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

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.

The 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
milanpasschier2's avatar
milanpasschier2
Helper I
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

    Milan,

    Thank you so much!