cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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

)
)
))

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.

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

6 REPLIES 6
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.

Super User

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

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.

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

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.

Super User

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

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.

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

Frequent Visitor

@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

Super User

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

)
)
))

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.

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors