cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Murf
New Member

Is Power BI the tool that I need?

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! 

1 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:

image.png

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:

image.png

Hopefully, this provides you with some ideas.

 

Enjoy Power BI,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
Murf
New Member

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!

 

 

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:

image.png

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:

image.png

Hopefully, this provides you with some ideas.

 

Enjoy Power BI,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

v-stephen-msft
Community Support
Community Support

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
Community Champion
Community Champion

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors