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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

Europe Fabric Conference

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.