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

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

Reply
Sunilkulkarni
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.

 

Week1Day2 Days3 Days4Days5Days
11035304510
2     
3     
4     
3 ACCEPTED SOLUTIONS
Daniel29195
Super User
Super User

@Sunilkulkarni 

sample data i used . 

Daniel29195_4-1706909477402.png

 

output 

Daniel29195_2-1706909379636.png

 

 

 

steps : 

step 1 : 

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

Daniel29195_3-1706909396180.png

 

 

step 2 :  create this measure

users attendance = 
Var ds = 
ADDCOLUMNS(
    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! 🤠

 

View solution in original post

@Sunilkulkarni

 

if you want totals at the row level 

use this modified version of the code : 

users attendance = 
Var ds = 
ADDCOLUMNS(
    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]
)

Daniel29195_0-1706910592859.png

 

 

 

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! 🤠

 

 

 

View solution in original post

@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! 🤠

 

View solution in original post

13 REPLIES 13
Sunilkulkarni
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

hello @Sunilkulkarni 

output 

Daniel29195_0-1707325529624.png

 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 = 
ADDCOLUMNS(
    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! 🤠

 

 

 

 

 

 

Hi @Daniel29195 

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

@Sunilkulkarni 

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

Daniel29195_0-1707472638399.png

 

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.

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

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 =
ADDCOLUMNS(
    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))
)

@Sunilkulkarni 

 

@Sunilkulkarni 

output base on sample data : 

Daniel29195_1-1707576099526.png

 

sample data : 

 

users attendece % = 
Var ds = 
ADDCOLUMNS(
    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! 🤠

 

 

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.

Sunilkulkarni_0-1707585356583.png

 

Daniel29195
Super User
Super User

@Sunilkulkarni 

sample data i used . 

Daniel29195_4-1706909477402.png

 

output 

Daniel29195_2-1706909379636.png

 

 

 

steps : 

step 1 : 

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

Daniel29195_3-1706909396180.png

 

 

step 2 :  create this measure

users attendance = 
Var ds = 
ADDCOLUMNS(
    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! 🤠

 

@Sunilkulkarni

 

if you want totals at the row level 

use this modified version of the code : 

users attendance = 
Var ds = 
ADDCOLUMNS(
    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]
)

Daniel29195_0-1706910592859.png

 

 

 

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! 🤠

 

 

 

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.

@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! 🤠

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.