Is Power BI the tool that I need?

[Murf]

Hi, I am looking for a solution to a problem and I have a feeling Power BI might be the solution but I'm not sure. So before I dive in and start learning I thought I'd ask to make sure I'm on the right track. 

I have a SharePoint list of monthly attendance for my organization. Each day class attendance is logged by a teacher for each student. Saved to the SharePoint list each day is a record for each student that indicates either present or absent. 

What I would like to do is be able to visualize which students are approaching 3 absences with the month and  which already have three.  The problem is I'm not sure how to count instances per student, taking into consideration only the absent instances and only the ones within a particular month.  Does that make sense? Is this even possible? Is Power BI the thing that might allow me to do this or is there some SharePoint function I should be looking into?

Really just looking for someone to tell me where to start looking. 

Thanks! 

[Murf]

Thank you all! I've taken the plunge and committed to learning this to figure out what I need! I've started to watch a bunch of videos and read here in the forums about DAX, CALCULATE, DIVIDE, etc. but I'll post here exactly what I am trying to do and maybe somebody can talk me through it! 

Basically, I have a sharepoint list of attendance. I've redacted the names and such and posted an excel version of my data here for reference.  

Basically, for any given date (or stretch of dates) I'd like to be able to tell what our attendance is.  So how many "Here", "Late", "Excused" divided by total number of instances for that date.  

Any thoughts on how I'd be able to go about doing this? I know I could do a COUNT of the attendance column, but I don't know how to do a COUNT of all the instances on a particular day/range.  I am also not sure how out of all of those instances I'd COUNT all of the "Here" "Late" and "Excused" and not the "Not Here".  Maybe this second question is the same logic, some kind of filter function? I'm not sure.  

Eventually, I'd like to be able to generate averages by group or by grade as well, but I feel like if I can wrap my head around by date, I can apply that to the others.  

Thanks for any help you might be able to offer!

[TomMartens · Accepted solution]

Hey @Murf ,

welcome to the wonderful world of Power BI!

Based on the sample data you provided I created some things that hopefully get you started on your journey.

I used this DAX statement to create a dedicated calendar table:

Calendar = 
var DateStart = MIN( 'Table_query__5'[Date] )
var DateEnd = MAX( 'Table_query__5'[Date] )
return
ADDCOLUMNS(
    CALENDAR( DateStart , DateEnd )
    , "Year-MM" , FORMAT( ''[Date] , "YYYY-MM" )
    , "Year" , YEAR( ''[Date] )
)

For a couple of reasons, it's always a good idea to have a dedicated calendar table. Almost anything is explained here: Time patterns – DAX Patterns

I created a relationship between the calendar table and the table from the Excel you provided:

Then I created some measures, I started with a base measure that counts the student ID, here I'm using DISTINCTCOUNT, as the attendance during a month might vary:

attendance (ms) = DISTINCTCOUNT( 'Table_query__5'[StudentID] )

Then I created different measures that are using/re-using the base measure.

attendance (all)

attendance (all) = 
CALCULATE(
    [attendance (ms)]
    , ALL( Table_query__5[Attendance] )
) 

absent (all)

absent (all) = 
CALCULATE(
    DISTINCTCOUNT( 'Table_query__5'[StudentID] )
    , 'Table_query__5'[Attendance] <> "Here"
) 

absent (all) / attendance (all)

absent (all) / attendance (all) = 
DIVIDE(
    [absent (all)]
    , [attendance (all)]
)

And a little screenshot showing two table visuals using different granularities from the Calendar table:

Hopefully, this provides you with some ideas.

Enjoy Power BI,

Tom

[v-stephen-msft]

Hi @Murf ,

You only need to connect to your SharePoint data source in Power BI Desktop, and then use the slicer or create the measure with DAX to filter the absent students.

If you are still confused, you can provide some dummy data and give the corresponding expected result. I'll try my best to help.

By the way, welcome to Power BI.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aj1973]

Hi @Murf 

Yes dive in. Your request is just a piece of cake for even a strater in Power bi. With Power Bi community you certainly won't get lost and all your issue could be answered in here. One thing to add, if you are not going to share your report/dashboard with others (self use) then a just free license can do the work for you.

Good luck.

[mahoneypat]

You defintely can do this with Power BI, but I think it is smart to ask current users if it's possible before you start the journey to learn it yourself.  Once you have your data pulled in, you can post some mock data in this forum to get help with the specific measures you'll need.  Provide a link to an Excel or CSV data on OneDrive, Google Drive, etc. or a link to a pbix file that already has the data in it, to get the most specific help (don't paste an image of the data).  Here is a link that might help you get started.

Create a report on a SharePoint List in Power BI Desktop - Power BI | Microsoft Docs

Pat