Forum Discussion

CEllis's avatar
CEllis
Resolver I
1 year ago
Solved

Attendance Streaks

 

Happy New Year to you all.

 

Could one of you clever people point me to understanding how I can write a dax measure that would count the number of absence streaks a pupil has and reseting the counter until it starts again.

 

The column DAX Measure is what I am looking to add to the table

 

iddatemarkstatistical meaningDAX Measure
11/9/2024/Present 
11/9/2024IAbsent1
12/9/2024IAbsent2
12/9/2024\Present 
13/9/2024/Present 
13/9/2024

L

Absent1
14/9/2024LAbsent2
14/9/2024LAbsent3
15/9/2024\Present 
21/9/2024LAbsent1
21/9/2024LAbsent2
22/9/2024LAbsent3
22/9/2024LAbsent4
23/9/2024LAbsent5
23/9/2024LAbsent6
24/9/2024/Present 
24/9/2024\Present 
25/9/2024lAbsent1

 

 

Many thanks

 

 

 

 

 

 

 

 

 

 

Need Help
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    Hi CEllis 

    To count absence streaks for each pupil in DAX, you can create a measure that:

     

    Tracks consecutive absences (marked as "I" or "L").

    Resets the count when a "Present" mark ("/" or "") is encountered.

    The measure uses CALCULATE and FILTER to count rows for consecutive absences and reset the count when a present mark appears, ensuring that the absence streaks are tracked for each pupil individually.

     

    DAX:

     

    Absence Streaks = 
VAR CurrentStudent = 'Table'[id]
VAR CurrentDate = 'Table'[date]
VAR CurrentMark = 'Table'[mark]
VAR PreviousAbsence =
    CALCULATE(
        MAXX(
            FILTER(
                'Table',
                'Table'[id] = CurrentStudent && 'Table'[date] < CurrentDate && 
                ('Table'[mark] = "I" || 'Table'[mark] = "L")
            ),
            'Table'[date]
        )
    )
VAR AbsenceStreak =
    IF(
        CurrentMark = "I" || CurrentMark = "L", 
        IF(
            ISBLANK(PreviousAbsence),
            1, 
            1 + CALCULATE(
                COUNTROWS('Table'),
                FILTER(
                    'Table',
                    'Table'[id] = CurrentStudent && 
                    'Table'[date] > PreviousAbsence && 
                    'Table'[mark] = "I" || 'Table'[mark] = "L"
                )
            )
        ),
        BLANK()
    )
RETURN AbsenceStreak

     

     

    Did I answer your question? Mark my post as a solution, this will help others!
    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

     

3 Replies

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    Hi CEllis 

    To count absence streaks for each pupil in DAX, you can create a measure that:

     

    Tracks consecutive absences (marked as "I" or "L").

    Resets the count when a "Present" mark ("/" or "") is encountered.

    The measure uses CALCULATE and FILTER to count rows for consecutive absences and reset the count when a present mark appears, ensuring that the absence streaks are tracked for each pupil individually.

     

    DAX:

     

    Absence Streaks = 
VAR CurrentStudent = 'Table'[id]
VAR CurrentDate = 'Table'[date]
VAR CurrentMark = 'Table'[mark]
VAR PreviousAbsence =
    CALCULATE(
        MAXX(
            FILTER(
                'Table',
                'Table'[id] = CurrentStudent && 'Table'[date] < CurrentDate && 
                ('Table'[mark] = "I" || 'Table'[mark] = "L")
            ),
            'Table'[date]
        )
    )
VAR AbsenceStreak =
    IF(
        CurrentMark = "I" || CurrentMark = "L", 
        IF(
            ISBLANK(PreviousAbsence),
            1, 
            1 + CALCULATE(
                COUNTROWS('Table'),
                FILTER(
                    'Table',
                    'Table'[id] = CurrentStudent && 
                    'Table'[date] > PreviousAbsence && 
                    'Table'[mark] = "I" || 'Table'[mark] = "L"
                )
            )
        ),
        BLANK()
    )
RETURN AbsenceStreak

     

     

    Did I answer your question? Mark my post as a solution, this will help others!
    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

     

    • CEllis's avatar
      CEllis
      Resolver I
      1 year ago

      Poojara_D12 

       

      Thanks for this it works really well.

       

      Should the number increment by 1 to get the cummulative total not just a 1 ?

       

       

       

       

       

       

       

       

      • Lucie_A_B's avatar
        Lucie_A_B
        New Member
        3 months ago

        Hi!

        I know this was a long time ago, but did you get it to work? 

        It seems like a great solution but I need the increments as well.

        Thank you 🙂