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
chang_jp
Frequent Visitor

How to count only one attendance per day with DAX

I have about 200 hundread students atending class and each class they must fill a form. However there are students sending more than once a day and I can not block for one responce a day.  how ever i would like to count how my responses or attendences  I have. 
I have the DAX below however it gives me all the count of responses from forms. So if one student responded 2 times , it count 2 times. How can i change to i can have it count only one per day per student? By the way i calculate the atendence per quater of the year and my students must atend at least 16 times. 

 

M_Q1_Attendance Count = 
CALCULATE(
    COUNT('礼拝Forms'[Completion time]),
    FILTER(
        '礼拝Forms',
        '礼拝Forms'[Completion time] >= DATE(2023,4,1) &&
        '礼拝Forms'[Completion time] <= DATE(2023,6,5)
    )
)

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Assuming the Completion time has Date data type, create a relationship (Many to One and Single) between the Completion time and Date column.  To your visual, drag Date from the Calendar Table.  Write this measure

Number of students = distinctcount(Data[Student ID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Assuming the Completion time has Date data type, create a relationship (Many to One and Single) between the Completion time and Date column.  To your visual, drag Date from the Calendar Table.  Write this measure

Number of students = distinctcount(Data[Student ID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much. it worked just fine. 

M_Q1_dist_Attendance Count = 
CALCULATE(
    distinctcount('礼拝Forms'[Completion time]),
    FILTER(
        '礼拝Forms',
        '礼拝Forms'[Completion time] >= DATE(2023,4,1) &&
        '礼拝Forms'[Completion time] <= DATE(2023,6,5)
    )
)
amitchandak
Super User
Super User

@chang_jp . in case Completion time does not have time stamp

 

else create a date value column first and use that

 

Completion date = datevalue([Completion time])

 

M_Q1_Attendance Count =
CALCULATE(
COUNTrows(Summarize('礼拝Forms','礼拝Forms'[Student],'礼拝Forms'[Completion time])),
FILTER(
'礼拝Forms',
'礼拝Forms'[Completion time] >= DATE(2023,4,1) &&
'礼拝Forms'[Completion time] <= DATE(2023,6,5)
)
)

thank you very much for your input. I will try your advice. however the distinctcount worked just fine. 
thank you very much for your advice.

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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