- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX to calculate Number of days presence in a ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

02-02-2024
06:19 AM

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 |

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-02-2024
01:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-02-2024
01:50 PM

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]
)
```

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-04-2024
03:34 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-06-2024
07:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-07-2024
09:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-09-2024
01:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-09-2024
01:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-09-2024
09:14 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-09-2024
09:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-10-2024
01:25 AM

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

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-10-2024
06:42 AM

output base on sample data :

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-10-2024
09:17 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-02-2024
01:31 PM

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 =
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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-02-2024
01:50 PM

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]
)
```

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-04-2024
02:40 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-04-2024
03:34 AM

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

94 | |

86 | |

78 | |

70 | |

63 |

Top Kudoed Authors

User | Count |
---|---|

114 | |

101 | |

97 | |

66 | |

59 |