topic Re: Filter Context within variable in DAX Commands and Tips

Filter Context within variable

Turduckin — Fri, 03 Jun 2022 18:47:10 GMT

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

Re: Filter Context within variable

ManguilibeKAO — Sat, 04 Jun 2022 17:11:26 GMT

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.

Re: Filter Context within variable

Turduckin — Mon, 06 Jun 2022 18:33:40 GMT

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

Re: Filter Context within variable

ManguilibeKAO — Tue, 07 Jun 2022 00:25:37 GMT

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.

Re: Filter Context within variable

Turduckin — Tue, 07 Jun 2022 16:08:05 GMT

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

Re: Filter Context within variable

Turduckin — Tue, 07 Jun 2022 17:28:52 GMT

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.

Re: Filter Context within variable

v-chenwuz-msft — Wed, 08 Jun 2022 10:12:22 GMT

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.

Best Regards

Community Support Team _ chenwu zhu

Re: Filter Context within variable

milanpasschier2 — Wed, 08 Jun 2022 15:57:41 GMT

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

Re: Filter Context within variable

milanpasschier2 — Wed, 08 Jun 2022 16:02:14 GMT

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

Re: Filter Context within variable

Turduckin — Wed, 08 Jun 2022 17:22:43 GMT

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

Re: Filter Context within variable

Turduckin — Wed, 08 Jun 2022 17:27:33 GMT

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.

Re: Filter Context within variable

milanpasschier2 — Wed, 08 Jun 2022 19:06:10 GMT

Ty, i'll have a look tomorrow 🙂

Re: Filter Context within variable

milanpasschier2 — Sat, 11 Jun 2022 08:33:08 GMT

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

Re: Filter Context within variable

Turduckin — Mon, 13 Jun 2022 15:52:06 GMT

Milan,

Thank you so much!