Forum Discussion
Counting iteration under a category- count if (containsstring)/match
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
7 Replies
what's the calculation logic for column F? not quite clear about the output.
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:
Scheduled cohort transfer in (count)- column F =Calculate(COUNTROWS(new_pos),ALLEXCEPT('new_pos',new_pos[Transfered into Cohort]))
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"))+0pls see the attachment below
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
