Forum Discussion

yamacha's avatar
yamacha
Icon for Helper I rankHelper I
1 year ago
Solved

Attendance List with two input table

Hi folks

 

I'm requested to make a dashboard to estimate the attendance of courses as below.

The challenge faced is to have the full list of enrolled folk + those not enrolled but still attended, for each session, and show N for those not enrolled and Y for those absent. 

I tried all what I have but still can't reach it... 

Thanks in advance.

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    These calculated column formulas work

    Enrolled = if(ISBLANK(CALCULATE(COUNTROWS('Enrol list'),FILTER('Enrol list','Enrol list'[Course]=EARLIER(Data[Course])&&'Enrol list'[Name]=EARLIER(Data[Attendance])))),"N",BLANK())
    Absense = if(ISBLANK(CALCULATE(COUNTROWS(Attendance),FILTER(Attendance,Attendance[Date]=EARLIER(Data[Date])&&Attendance[Course]=EARLIER(Data[Course])&&Attendance[Attendance]=EARLIER(Data[Attendance])))),"Y",BLANK())

    Hope this helps.

     

     

5 Replies

    • yamacha's avatar
      yamacha
      Icon for Helper I rankHelper I
      1 year ago

      Hi Elitesmitpatel

       

      The input and output are all shown in the screenshot, and I attached the editable table below. Thanks.

      Enroll List  CourseDateAttendance CourseDateAttendanceEnrolledAbsense
      CourseName A2024/9/1Mike A2024/9/1Mike　　
      AMike A2024/9/1Tom A2024/9/1Tom　　
      ATom A2024/9/1Peter A2024/9/1Peter　　
      APeter A2024/9/1Adam A2024/9/1AdamN　
      BMary A2024/9/8Peter A2024/9/8Mike　Y
      BWendy A2024/9/8Vivian A2024/9/8Tom　Y
      BVivian A2024/9/8Mary A2024/9/8Peter　　
      …  B2024/9/15Mary A2024/9/8Vivian N　
         B2024/9/15Wendy A2024/9/8MaryN　
         B2024/9/15Vivian B2024/9/15Mary　　
         B2024/9/22Mary B2024/9/15Wendy　　
         B2024/9/22Wendy B2024/9/15Vivian　　
         B2024/9/22Heather B2024/9/22Mary　　
         …   B2024/9/22Wendy　　
             B2024/9/22Vivian　Y
             B2024/9/22HeatherN　
             …    

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        1 year ago

        Hi,

        These calculated column formulas work

        Enrolled = if(ISBLANK(CALCULATE(COUNTROWS('Enrol list'),FILTER('Enrol list','Enrol list'[Course]=EARLIER(Data[Course])&&'Enrol list'[Name]=EARLIER(Data[Attendance])))),"N",BLANK())
        Absense = if(ISBLANK(CALCULATE(COUNTROWS(Attendance),FILTER(Attendance,Attendance[Date]=EARLIER(Data[Date])&&Attendance[Course]=EARLIER(Data[Course])&&Attendance[Attendance]=EARLIER(Data[Attendance])))),"Y",BLANK())

        Hope this helps.