Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chang_jp
Frequent Visitor

how to calculate more than 2 atendences per week

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? 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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

)
)
))



Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

6 REPLIES 6
chang_jp
Frequent Visitor

@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. 


 

@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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

chang_jp
Frequent Visitor

@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.

@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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

スクリーンショット 2023-02-20 105259.png

@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
Super User
Super User

@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

)
)
))



Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors