Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Powerlearn_95
Frequent Visitor

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. 

Powerlearn_95_1-1695682386081.png

 

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 7
Powerlearn_95
Frequent Visitor

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

Powerlearn_95_0-1695724071725.png

 

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.

@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

11.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

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:

Powerlearn_95_0-1695830778118.png



Student IDStudent Scheduled GradActual exit dateTransferred into cohortGraduation StatusScheduled Cohort transfer in (count)Degree
1112/4/20232/4/2023 graduated on-time2English
1123/11/20232/4/20232/4/2023Graduated late1English
1133/11/20232/4/20232/4/2023Graduated late1English
1144/12/20234/12/2023 graduated on-time0English
1155/16/20233/11/20233/11/2023graduated early0English
1166/12/20236/12/2023 graduated on-time0English
1172/4/20232/4/2023 graduated on-time2Mathematics
1183/11/20232/4/20232/4/2023Graduated late1Mathematics
1193/11/20232/4/20232/4/2023Graduated late1Mathematics
1204/12/20234/12/2023 graduated on-time0Mathematics
1215/16/20233/11/20233/11/2023graduated early0Mathematics
1226/12/20236/12/2023 graduated on-time0Mathematics
1112/4/20232/4/2023 graduated on-time2Engineering
1123/11/20232/4/20232/4/2023Graduated late1Engineering
1133/11/20232/4/20232/4/2023Graduated late1Engineering
1144/12/20234/12/2023 graduated on-time0Engineering
1155/16/20233/11/20233/11/2023graduated early0Engineering
1166/12/20236/12/2023 graduated on-time0Engineering

@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

11.PNG





Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors