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

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