Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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].
Solved! Go to 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
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.
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.
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.
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
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.
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.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |