Tying multiple tables, columns and string names to populate a visual table

[RyanLMoran]

Hello:

     I'm hoping that the PowerBI community can provide guidance for me on what I'm trying to create here.  I need to make 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

I'm pulling this data in from an LMS data/table structure that we have in a different platform.  To identify the teacher we assign them an indenx number and the grade is listed as a string in the name.  So, in that platform I identified that I could find the total first grade enrollments for a teacher based on string name and the teacher index as shown here:
SELECT SectionIndex, Name FROM Sections WHERE [Status] = 'ACTIVE' AND TeacherIndex = 143 AND Name LIKE '%First Grade%'

I'm looking for help to figure out how I would transfer this SQL query to the PowerBI space to be able to populate my table visual with this data.  Thank you.

Data that I have pulled in to the PowerBI Space:

[TheoC]

Hi @RyanLMoran 

You can use a Matrix table to establish what you're looking for.  Columns will be the Grade, Rows will be the Teacher Index / Name, and so on.  You may need to extend your SQL query to grab the Grade if it's not there. But nonetheless, you can add multiple rows / columns and values to using this visual. 

Hope this helps!
Theo

[RyanLMoran]

Hi Theo,

    Thank you.  Yes, that makes sense to populate the Matrix table like that.  The Section name contains a string like: First Grade Health, First Semester...... etc..  I need to be able to essentially look into the Name column and if it has First Grade to populate the First Grade spot, repeat for Second grade, third grade, etc..  We don't have a column  or field that states "Grade" that has first grade.  So, I need to be able to look at the string and count that.  I'm not sure how to do that in PowerBI.  Thanks again.

[TheoC]

Hi @RyanLMoran 

You can create a calculated column and use switch / find to provide an output that you're wanting.  Refer here (Solved: Is there a way to use SWITCH with CONTAINS? - Microsoft Power BI Community).

If you can use Power Query, I'd recommend using Conditional Column using "contains" and go from there (Power Query - If Statements for Conditional Logic - Excel Off The Grid).

Hopefully this helps and is what you're looking for.

Theo 🙂

[TheoC]

@RyanLMoran below is an example for your scenario with a Calculated Column:

Grade = 
SWITCH (
    TRUE (),
    ISERROR ( FIND ( "First Grade", 'Sections'[Name] ) ) <> TRUE (), "First Grade",
    ISERROR ( FIND ( "Second Grade", 'Sections'[Name] ) ) <> TRUE (), "Second Grade",
    ISERROR ( FIND ( "Third Grade", 'Sections'[Name] ) ) <> TRUE (), "ThirdGrade",
    ISERROR ( FIND ( "Fourth Grade", 'Sections'[Name] ) ) <> TRUE (), "Fourth Grade",
    ISERROR ( FIND ( "Fifth Grade", 'Sections'[Name] ) ) <> TRUE (), "Fith Grade",
    ISERROR ( FIND ( "Sixth Grade", 'Sections'[Name] ) ) <> TRUE (), "Sixth Grade",
"No Grade Allocated" )

 Just update the terms you're wanting to find in FIND ( "..."  and adjust the output you want at the end of the line.

Hope this helps mate.

Theo 

[RyanLMoran]

Thanks Theo! That's super helpful ( I appreciate your patience as I'm trying to learn and wade through all of this PowerBI information).  A quick question or two.  If I wanted to just look though the Sections Table and Name field to look for the strings Kindergarten, First Grade, Second Grade, etc... and to give me the sum for all grades in total and/or each individual grade would I use Sum expression?  The Expression that you've helped me with looks like it will list in a long table the teacher and the grade.  What I'm trying to achieve is to list the teacher and the Sum of their First Grade Enrollments. 

And to not list if their not teaching an elementary course

Like 

Elementary Enrollments = SWITCH (

TRUE (), ISERROR ( FIND ( "Kindergarten", 'Sections'[Name] ) ) <> TRUE (), "Kindergarten", SUM(Sections[Name]),

ISERROR ( FIND ( "First Grade", 'Sections'[Name] ) ) <> TRUE (), "First Grade",SUM(Sections[Name])

ISERROR ( FIND ( "Second Grade", 'Sections'[Name] ) ) <> TRUE (), "Second Grade",SUM(Sections[Name])

ISERROR ( FIND ( "Third Grade", 'Sections'[Name] ) ) <> TRUE (), "ThirdGrade",SUM(Sections[Name])

ISERROR ( FIND ( "Fourth Grade", 'Sections'[Name] ) ) <> TRUE (), "Fourth Grade",SUM(Sections[Name])

ISERROR ( FIND ( "Fifth Grade", 'Sections'[Name] ) ) <> TRUE (), "Fith Grade",SUM(Sections[Name])

ISERROR ( FIND ( "Sixth Grade", 'Sections'[Name] ) ) <> TRUE (), "Sixth Grade",SUM(Sections[Name]) )

? 

THank you!

[TheoC]

Hi @RyanLMoran 

If I understand correctly, you should be able to create a new measure using SUM (as you raised), and drag that into the table in the Values space.

You can simply filter out blanks in the Filter pane of the visual by clicking on the amount and unpicking Blank.

Hope this helps mate.

Theo