cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanLMoran
Helper I
Helper I

Calculating number of times a word (string) is in a dataset

Hello:

 

     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

Teacher names

Grade K

Grade 1

Grade 2

Grade 3

Grade 4

Grade 5

Total

Teacher 1

 

 

 

 

 

 

 

Teacher 2

 

 

 

 

 

 

 

Teacher 3

 

 

 

 

 

 

 

Total

 

 

 

 

 

 

Total

 

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: 

RyanLMoran_0-1643329009952.png

I've brought this data in to my PowerBI Space to try and get this data compiled:

RyanLMoran_1-1643329182208.png

 

RyanLMoran_2-1643329212397.png

 

1 REPLY 1
amitchandak
Super User
Super User

@RyanLMoran , Based on what I got

if your data is like the first screenshot. Comma-separated. Then you can split that in power query in rows.

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

 

You can also try text filter if that can help

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381309?src=office&tab=Overview

 

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors