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/1SGXJN1Wo-SoqGJpe0ZwsPoRlYYr3-o0p/view?usp=sharing 

Most fields should be self explanatory, but the Sequence ID is some maths I'm doing in SQL to separate non-consecutive enrollment terms. So, a student who enrolled in classes across 3 quarters with a break between two would look something like:


Note: This is based on academic year so, the Year is effectively the graduating year, and could also be written as Summer 2019-2020

PaulDBrowngot me a lot further in my hunt with the following post How to count values that satisfy multiple conditions in a column but I cannot get the second part of my filter to work (only counting their first fall and winter quarter).

My goal is to only look at records that have the lowest Sequence ID for each Student ID and that have a sequential "Fall" [Year] -> "Winter" [Year].


  • 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

13 Replies

  • ManguilibeKAO's avatar
    ManguilibeKAO
    Resolver I
    4 years ago

    HI Turduckin,

     

    Could you show a sample of the output you are looking for?  Two or three rows in this output  should help  us to suggest you a solution to your problem.

     

    Best regards.

    • Turduckin's avatar
      Turduckin
      Frequent Visitor
      4 years ago

      Sorry about that, I added it to my uploaded pbix model, but I didn't provide any comments on it.

      I'd want my output to look something like this, but my current results are either fall, or winter. When really I want the fall followed by the subsequent winter quarter.

      I'm getting the "Fall to Winter" column with the following DAX:

       

      Fall to Winter = 

      VAR _FirstSequence = [First Sequence]

      VAR _Fall =
      CALCULATETABLE(
      VALUES(StudentPersistance[Student ID]),
      FILTER(StudentPersistance, StudentPersistance[Quarter Name] = "Fall"),
      StudentPersistance[Sequence ID] = _FirstSequence)

      VAR _Winter =
      CALCULATETABLE(
      VALUES(StudentPersistance[Student ID]),
      FILTER(StudentPersistance, StudentPersistance[Quarter Name] = "Winter"),
      StudentPersistance[Sequence ID] = _FirstSequence)

      VAR _Intersect = 
      CALCULATETABLE(
      INTERSECT(_Fall, _Winter))

      Return

      COUNTROWS(_Intersect)

      [First Sequence] is the following measure:

      First Sequence = 
      CALCULATE(
      MIN(StudentPersistance[Sequence ID]),
      ALLEXCEPT(StudentPersistance, StudentPersistance[Student ID]))
      • ManguilibeKAO's avatar
        ManguilibeKAO
        Resolver I
        4 years ago

        Hello Turduckin,

         

        Would you want to explain, how in your output, you obtain a value of 4  in   Fall to Winter, for the year 2019?

         

        Best regards.