Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Turduckin
Frequent Visitor

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:

Turduckin_0-1654281895901.png


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


1 ACCEPTED SOLUTION

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

View solution in original post

13 REPLIES 13
ManguilibeKAO
Resolver I
Resolver I

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.

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.

Turduckin_0-1654540128009.png

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

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.

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

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.

 

Turduckin_0-1654622923547.png

 

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

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

 

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

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

Milan,

Thank you so much!

Ty, i'll have a look tomorrow 🙂

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.

vchenwuzmsft_0-1654683106953.png

 

Best Regards

Community Support Team _ chenwu zhu

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors