cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## DAX to calculate Number of days presence in a week for Attendance

Hi

This is for the Attendance module, for a school,

I have a Table, Attendance, which has Dates and students Present or Absent, if present it is "1" and Absent is "0". in the coloumn, school presence .

I want to represent in the Stacked bar chart as below.  below Num are no. of students present

Ex : In Week 1 for 1 day in a week how many Present, 2days How many, 3 days, 4days.....

What is The DAx to calculate this. and how to represent in Stacked Bar chart.

 Week 1Day 2 Days 3 Days 4Days 5Days 1 10 35 30 45 10 2 3 4
3 ACCEPTED SOLUTIONS
Super User

sample data i used .

output

steps :

step 1 :

create the following table :  in power query . ( use enter data )

step 2 :  create this measure

``````users attendance =
Var ds =
VALUES(ATTENDANCE[STUDENT ID]),
"@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

RETURN
SELECTCOLUMNS(
FILTER(
ds1,
value([@x]) = value(dayss)
),
[count]
)``````

let me know if this works for you

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

@Sunilkulkarni

if you want totals at the row level

use this modified version of the code :

``````users attendance =
Var ds =
VALUES(ATTENDANCE[STUDENT ID]),
"@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = values(days[days])

return

SUMX(
FILTER(
ds1,
value([@x]) in (dayss)
),
[count]
)``````

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

@Sunilkulkarni  sure.

step1 :

var ds

will get the sum of days where the student were present per week per student.

now that that you have w temp table having the students, and each student how many times was present .

you need a way to count the nb of students per the nb of days present .

step2:

so this is where groupby functions comes in handy .( u can use summarize. it also works  )

using groupby, you groupby the calculated column created in step 1 ,  which is the nb of days present,

and you get the count of students .

last step :

since you have the nb of days in the columns : 1 ,2 , 3, 4, 5

you return the sum of the count of students calculated in the previous step (step2) ,

and you filter base on the nb of days existing in the filter context ( example. in column 1 in the matrix, you have nbofdays  = 1 , thus you need to get the sum of count of students where days ( [@x] = 1 ) ,

and so on .

hope this makes sense .

If my answer helped sort things out for you, i would appreciate a thumbs up👍and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

13 REPLIES 13
Frequent Visitor

Its Very Helpful, Thanks a ton.

I have another situation to calculate the % of Presence , I need to calculate % of Students presence only for students who have attended school 4 days and above for a week, This count of students will be divided by total no. of students. Kindly help with DAX

Super User

hello @Sunilkulkarni

output

base on the sample data above, we have 1 student that did attend >=4days   .

thus 1/10 = 10%

if this logic is correct kindly use this measure :

``````users attendece % =
Var ds =
VALUES(tbl[studentid]),
"@x" ,  CALCULATE(SUM(tbl[schoole presence]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

var c =
SELECTCOLUMNS(
FILTER(
ds1,
value([@x]) = 4
),
[count]
)

return
c / CALCULATE(DISTINCTCOUNT(tbl[studentid]), all(tbl))``````

let me know if this works for you .

NB: if it is possible, i would suggest to create the table in the backend in the case that this structure of table is needed for further calculations .  and this way your dax code would be much simpler .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Frequent Visitor

Thanks for the DAX, But it is not working, im getting 1.72% which i feel not correcrt out of 5000 Students.

If i want to calculate for >2 days presence how would i do that, i tried in above Dax its not working. For Ex in above table ,  it has to 5 Students whoc are more than 2 days presence in a week

Super User

did you change from 4 to 2  in the code in the section at the end?

Frequent Visitor

Yes I did, but if i cange to 2 , it will only calculate Presence 2, i want to count students who is present >, 2,3 or 4 ,5days.

Super User

@Sunilkulkarni
ok i got you

Change the static number to

selectedvalue( days[days] )

Now this should work base on the days in the filter context

now if you only want to calculate when you have days >=2

then  you can simply at the return add a condition that if selectedvalue( days[days] ) <2 , blank(), c/ calculate(distinctcount(tbl[studentid) , all(tbl))

Frequent Visitor
No Daniel, Its not working, please find below code i used. I want calculate %
Total No. of Students Present for more than 3days a week/toal No. of students.
In Total No. of Students Present for more than 3days a week it should add all the students who have come more than 3 days a week for a month
Users attendece % =
Var ds =
VALUES(Attendance[StuID]),
"@x" ,  CALCULATE(SUM(Attendance[School_Presence_Count]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(WeekDays[Day])

var c =
SELECTCOLUMNS(
FILTER(
ds1,
value([@x])=selectedvalue( WeekDays[Day])  ),[count]
)

return
if (selectedvalue( WeekDays[Day]) <2 , blank(), c / CALCULATE(DISTINCTCOUNT(Attendance[StuID]), all(Attendance))
)
Super User

output base on sample data :

sample data :

``````users attendece % =
Var ds =
VALUES('Table'[student id]),
"@x" ,  CALCULATE(SUM('Table'[presence]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

RETURN

SUMX(
FILTER(
ds1,
value([@x]) in VALUES(dimdays[days]) &&  [@x]>=2
),
[count]
)
/  CALCULATE(DISTINCTCOUNT('Table'[student id]), all('Table'))

``````

If my answer helped sort things out for you, i would appreaciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Frequent Visitor

Hi , Im getting below out put

In the card it is showing very less %, Weekwise total % is correct, but when it is cumulative in single card, % is wrong, how to Fix.

Super User

sample data i used .

output

steps :

step 1 :

create the following table :  in power query . ( use enter data )

step 2 :  create this measure

``````users attendance =
Var ds =
VALUES(ATTENDANCE[STUDENT ID]),
"@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

RETURN
SELECTCOLUMNS(
FILTER(
ds1,
value([@x]) = value(dayss)
),
[count]
)``````

let me know if this works for you

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Super User

@Sunilkulkarni

if you want totals at the row level

use this modified version of the code :

``````users attendance =
Var ds =
VALUES(ATTENDANCE[STUDENT ID]),
"@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
)

var ds1 =
GROUPBY(
ds,
[@x],
"count" , countx(CURRENTGROUP(), 1)
)

var  dayss = values(days[days])

return

SUMX(
FILTER(
ds1,
value([@x]) in (dayss)
),
[count]
)``````

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Frequent Visitor

Thanks Daniel for the solution, I was struggling a lot to solve this issue, Thanks a ton for the help. if possible can you please explain how this measure works.

Super User

@Sunilkulkarni  sure.

step1 :

var ds

will get the sum of days where the student were present per week per student.

now that that you have w temp table having the students, and each student how many times was present .

you need a way to count the nb of students per the nb of days present .

step2:

so this is where groupby functions comes in handy .( u can use summarize. it also works  )

using groupby, you groupby the calculated column created in step 1 ,  which is the nb of days present,

and you get the count of students .

last step :

since you have the nb of days in the columns : 1 ,2 , 3, 4, 5

you return the sum of the count of students calculated in the previous step (step2) ,

and you filter base on the nb of days existing in the filter context ( example. in column 1 in the matrix, you have nbofdays  = 1 , thus you need to get the sum of count of students where days ( [@x] = 1 ) ,

and so on .

hope this makes sense .

If my answer helped sort things out for you, i would appreciate a thumbs up👍and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Announcements

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

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors