Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
Thank you for reviewing my questions.
I've been tackling a problem in BI, but I haven't had much luck.
I am working with a model that analyzes student completion rate. Students can graduate early if they have some courses already completed before starting a program, can graduate late if they have an execuse such as a leave of absence, and then we have a group that will graduate on-time. This model doesn't count any drops or incompletes.
I am trying to find a formula that counts that number of transfer ins (as shown in column F) per student scheduled grad (column B).
Another issue I am having is although I have all date columns set to Data Type = Date, if I do a quick check (is C3= D3), I get false.
Any help would be greatly appreciated. Thank you
@ryan_mayu Thank you for your response.
Calculation logic for column F is as follows:
- First, student scheduled grad dates are all different cohort dates. Based on when the student started, the student is assigned a scheduled grad date (which is a cohort grad date)
- With that said, IF (Actual exit date =! Student scheduled grad), then the student did not graduate on-time (they either graduated early or late), these are transfer in students. Meaning these are the students that graduated with a cohort other than the one they were originally assigned when they started the program. From here, I created cell D to show what cohort they transferred into (Transfered into Cohort).
- The final goal is to count into column F wherever there is a date match in columns C or D using column B (student scheduled grad) as the reference date, but to only count this for transfer in students (students who graduated early/late; actual exit date =! Student Scheduled grad). Students that graduated on-time are not transfers in. For example, for scheduled grad date of 2/4/2023, count how many total cells in columns C or D that matches the 2/4/2023 scheduled grad date, where actual exit date =! Student Scheduled Grad) and count that in Column F. For 2/4/2023, we have 3 total matches in column C, but only two of those were transfers in and one was an on-time grad. Hence the count of 2 in Column F.
I assume there is a way to skip column D entirely and do all the calculations from column C alone, but I created column D to use the following formula to count using ALLEXCEPT as shown below, but I need a way to calculate this referencing the Student Scheduled Grad. Currently, I am using the following referencing column D, but I need to adjust it to reference column B instead:
Here is the table again for reference:
Project goal: For a given cohort (based on student scheduled grad- column B), count how many students were transfers in (those who joined the cohort from another cohort).
I hope this helps and sorry for the confusion.
you can try this
Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Actual exit date]=EARLIER('Table'[student scheduled grad])&&'Table'[Gradutation status]<>"on time"))+0
pls see the attachment below
Proud to be a Super User!
Wonderful, thank you! I am truly thanksful and grateful for your time and help!
I can see that this counts all the transfer ins referencing scheduled cohort. This is very close. If I wanted to do it in a way that categorizes the count by degree, can I add another filter. Or maybe do an iteration column so when I have it as a table in BI I can just filter by degree?
For example, if I have a larger dataset with varying degrees (i.e. Mathematics, English, Engineering, etc.), would I have to create an && filter that uses contains string and create a different column for each degree or is there a way to do it by degree. Refrence table below:
Student ID | Student Scheduled Grad | Actual exit date | Transferred into cohort | Graduation Status | Scheduled Cohort transfer in (count) | Degree |
111 | 2/4/2023 | 2/4/2023 | graduated on-time | 2 | English | |
112 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | English |
113 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | English |
114 | 4/12/2023 | 4/12/2023 | graduated on-time | 0 | English | |
115 | 5/16/2023 | 3/11/2023 | 3/11/2023 | graduated early | 0 | English |
116 | 6/12/2023 | 6/12/2023 | graduated on-time | 0 | English | |
117 | 2/4/2023 | 2/4/2023 | graduated on-time | 2 | Mathematics | |
118 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | Mathematics |
119 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | Mathematics |
120 | 4/12/2023 | 4/12/2023 | graduated on-time | 0 | Mathematics | |
121 | 5/16/2023 | 3/11/2023 | 3/11/2023 | graduated early | 0 | Mathematics |
122 | 6/12/2023 | 6/12/2023 | graduated on-time | 0 | Mathematics | |
111 | 2/4/2023 | 2/4/2023 | graduated on-time | 2 | Engineering | |
112 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | Engineering |
113 | 3/11/2023 | 2/4/2023 | 2/4/2023 | Graduated late | 1 | Engineering |
114 | 4/12/2023 | 4/12/2023 | graduated on-time | 0 | Engineering | |
115 | 5/16/2023 | 3/11/2023 | 3/11/2023 | graduated early | 0 | Engineering |
116 | 6/12/2023 | 6/12/2023 | graduated on-time | 0 | Engineering |
pls try this
Column = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Actual exit date]=EARLIER('Table'[student scheduled grad])&&'Table'[Graduation Status]<>"graduated on-time"&&'Table'[Degree]=EARLIER('Table'[Degree])))+0
Proud to be a Super User!
I've tried the following code:
Transfer in count across =
CALCULATE(
COUNTROWS(new_pos),
FILTER(
new_pos,
new_pos[Exitdatekey] = EARLIER(new_pos[ScheduledCohortGradDateKey]) &&
(
new_pos[GradStatus] = "Graduated Early" ||
new_pos[GradStatus] = "Graduated Late"
) &&
new_pos[new_degreeabbreviation] = EARLIER(new_pos[new_degreeabbreviation])
)
) + 0
Grad status has more fields so I filtered for Graduated Early and Graduated Late, but the values are a little off. But very close
what's the problem now? could you pls update the sample data which does not meet the expected output?
Proud to be a Super User!
what's the calculation logic for column F? not quite clear about the output.
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |