how to calculate more than 2 atendences per week

[chang_jp]

I work in a school and have a excel sheet with atendence report from 300 hundred students for a period of one year. 
Students must atend more than 2 times a week for the whole year.

1. I would like to calculate how many weeks each student atended more than 2 times a week.

2. I would like to calcutale also how many weeks each student could not atend more than 2 times a week. Which means the one atendence of the week is zero or one. 

Does anyone have any idea how to do this in query? 

[chang_jp]

@AllisonKennedy 

I figured out what was wrong!
First of all the problem was my lack of understanding of mesures.

1. I realized that there should be 2 mesures 
 1: this one is to count the the student atenndence

Attend Count = countrows(student_atendence)

2: this one filter the count acording the week of the atendence with a condition of 2 or more atendences per week.

Count Weeks Attend At Least Twice per Week = 

CALCULATE(
COUNTROWS (
FILTER (
VALUES( student_atendence[週の最終日].[Date] ), 
[Attend Count] >= 2
)
)
)

to make it work i deleted the SUMX funtion. still not sure why but with the SUMX i did not have the results. 

@AllisonKennedy 
thank you very much i could not be able to make without your help. 

[AllisonKennedy]

@chang_jp  Yes, sorry I will try to be more clear next time that they are two different measures. Glad it worked and thanks for sharing your solution. 

[chang_jp]

@AllisonKennedy 

Thank you so much for the fast reply

"but if it looks like two columns with name and date for attended only"
yes it is just like that.
I created a mesure and added the code, how ever it is giving me some problems.
I am not very familiar with codes so I would like to try a litle more. 
I will let you know if it works 
thank you so much.

[AllisonKennedy]

@chang_jp  Context of what you have in your visual will be very important balance with what you put in that code, so if you can share examples of what is and isn't working we can help further refine the correct code for your context. 

[chang_jp]

@AllisonKennedy 
fisrt of all thank you so much for the follow up.

so i tried adding the code, and i got few questions.

1. Is this code supposed to be all in one measure or separated?
2. Line 1 i could not add the collumn name, only the table. if i added it would give me an error
3. Same problem with line 6 , i could only add the table  and it is giving an error at line 5 not sure why.

4.Line 10 was the only one that i could add the collum name, how ever i could not understand how this code would do the caulations. don`t i need to add the end of the week, so i would calculte betwen the start and the end of each week?

i am very new with this code, so there are a millions things that i still do not understand about it.
thanks you 

[AllisonKennedy · Accepted solution]

@chang_jp 

Do you have a DimDate table? 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Create one if not, you'll need to add either 'Start of Week' or 'End of Week' to the one in the link above if you use it - you can do that in Power Query with Add Column > Date >

Then I need to know a bit more about how the attendance sheet looks, but if it looks like two columns with name and date for attended only, then you can create a measure:

Attend Count = countrows(table[Name])

Count Weeks Attend At Least Twice per Week = 
SUMX( DimStudent, 
CALCULATE(
COUNTROWS (
FILTER (
VALUES( DimDate[Start of Week] ), 
[Attend Count] >= 2

)
)
))