Filter Data by number of instances

[Murf]

I have attendance data, where on any given day a number of students are marked, "Here", "Not Here", or "Excused." So the Data over a month, has multiple instances of the same student being marked marked, Here, Not Here, or Excused.  I need a way of generating a list of all the students who have 3 or more "Not Here"s in a given month.  This list will represent students who are no categorized as Ineligible.  

I was thinking I could Filter the table to show only those marked as Not Here within the month of October for example. But from there is where I get stuck.  Within that filtered table, how do I then check to see if a student has 3 or more instances? Then, once I do that, how do I generate a list of all those students with three or more? 

If it helps, I've attached a screenshot of what my attendance table looks like.  

Any advice would be super helpful!  Thanks so much

[TheoC · Accepted solution]

Hi @Murf 

If you use a Matrix visual, you can set it up quite easily with your data as is:

Just structure the Matrix table as per below:

If you wanted to filter by Dates / Months, just use the Slicer visual and simply drag Date (Month) column into the slicer visual and you can slice and dice like that. 

If you wanted to have something such as "Accept" or "Flag" per student, you can use the following measure to produce the column "StudentsAbsent" on the right:

StudentsAbsent = 

VAR StudentsExcused = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( 'Table' , 'Table'[Attendance] = "Excused" ) )

RETURN 

SWITCH ( TRUE () , StudentsExcused >= 3, "Flag" , "Accept" )

[TheoC · Accepted solution]

Hi @Murf 

If you use a Matrix visual, you can set it up quite easily with your data as is:

Just structure the Matrix table as per below:

If you wanted to filter by Dates / Months, just use the Slicer visual and simply drag Date (Month) column into the slicer visual and you can slice and dice like that. 

If you wanted to have something such as "Accept" or "Flag" per student, you can use the following measure to produce the column "StudentsAbsent" on the right:

StudentsAbsent = 

VAR StudentsExcused = CALCULATE ( COUNTROWS ( 'Table' ) , FILTER ( 'Table' , 'Table'[Attendance] = "Excused" ) )

RETURN 

SWITCH ( TRUE () , StudentsExcused >= 3, "Flag" , "Accept" )

[Murf]

Oh this is super helpful and clever! Thanks so much!

One question though, what is Count of Attendance?

[TheoC · Accepted solution]

Hi @Murf, I am glad it is helpful! 

The "Count of Attendance" is simply the "Attendance" column being dragged into the visual and then using the little down arrow to select "Count". You can use that little arrow as a quick way of getting some simple outputs (i.e. Count, Distinct Count, Sum, etc., etc.) dependent upon the Data Type.

[Murf]

Oh wow, duh! Thank you!! You have just saved me a lot of time banging my head against the wall.  

[TheoC]

@Murf hehe - trust me, we have all been there. That's how we all started this wonderful Power BI journey (anyone who says otherwise is full of rubbish lol).