Forum Discussion
Counting iteration under a category- count if (containsstring)/match
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])))+0I'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 closewhat's the problem now? could you pls update the sample data which does not meet the expected output?