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

Get 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

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.