Calculating number of times a word (string) is in a dataset
I'm looking for some help with a few tables that I have to create. The first one is this:
Elementary Teacher by Grade Level Report
The grade levels are not set up with their own data set or column name. They are created in an LMS section and inside that section they are listed in a name field. What I need to do is to look through that name field and find all references of the grades and total them to the teacher. This way we can get a quick snapshot of how many enrollments each teacher has for each grade level.
For instance, when I run this SQL query in our LMS: SELECT SectionIndex, Name FROM Sections WHERE [Status] = 'ACTIVE' AND TeacherIndex = 143
I'm assuming that I need some measure that will look at the string for First Grade, or Second Grade, etc... and count those Active instances to give me the total that I'm looking to populate in the table that I'm trying to create?
It will return this data to me that tells me that this teacher (whom we have given the index number of 143) is teaching these first grade classes:
I've brought this data in to my PowerBI Space to try and get this data compiled: