Counting iteration under a category- count if (containsstring)/match

[Powerlearn_95]

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

[Powerlearn_95]

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

[ryan_mayu]

@Powerlearn_95 

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

[Powerlearn_95]

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

[ryan_mayu]

@Powerlearn_95 

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

[Powerlearn_95]

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

[ryan_mayu]

what's the problem now? could you pls update the sample data which does not meet the expected output?

[ryan_mayu]

what's the calculation logic for column F? not quite clear about the output.