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

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.

      • 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).