Count Maximum Consecutive Occurrences of a value

[Iam_Uday]

Hi,

I have a input table in this format,

Date	Name	Present or Absent
5/1/2022	A	Present
5/1/2022	B	Present
5/2/2022	A	Present
5/2/2022	B	Present
5/3/2022	A	Present
5/3/2022	B	Present
5/4/2022	A	Present
5/4/2022	B	Absent
5/5/2022	A	Present
5/5/2022	B	Present
5/6/2022	A	Absent
5/6/2022	B	Present

I need to generate a final visualization like below, Not sure how to calculate Highest No. of days Continously Present column

Name	Highest No.of days Continuously Present
A	5
B	3

Can anyone help me to get this column using Calculated Columns or Measures?
Any help is really appreciated

[tamerj1 · Accepted solution]

Hi @Iam_Uday 
Here is a soluation based on 2 columns and a measure https://www.dropbox.com/t/y15kpJR7MzVRVhsf

Calculated Column (Rank):

Rank = 
VAR Rank1 = 
    RANKX ( 
        CALCULATETABLE ( Attendance, ALLEXCEPT ( Attendance, Attendance[Name] ) ),
        Attendance[Date],, ASC, Dense 
    )
VAR Rank2 = 
    RANKX ( 
        CALCULATETABLE ( 
            Attendance, ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Present or Absent] ) ), 
            Attendance[Date],, ASC, Dense 
        )
RETURN
    IF (
        Attendance[Present or Absent] <> "Absent", 
        Rank1 - Rank2
    )

Calculated Column (# Days Present)

# Days Present = 
IF ( 
    NOT ISBLANK ( Attendance[Rank] ),
    COUNTROWS ( 
        CALCULATETABLE ( 
            Attendance, 
            ALLEXCEPT ( Attendance, Attendance[Name], Attendance[Rank] ) 
        ) 
    )
)

Measure

Max # Days Present = MAX ( Attendance[# Days Present] )

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like the below.

I numbered the measures to follow step by step.

Please check the attached pbix file.

1 P or A measure: = 
IF( SELECTEDVALUE(Data[PorA]) = "P", 0, 1)

2 Index measure: = 
CALCULATE (
    SUMX ( SUMMARIZE ( Data, 'Name'[Name], 'Calendar'[Date] ), [1 P or A measure:] ),
    'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)

3 Max of Countrow same index: = 
IF( ISFILTERED('Name'[Name] ),
MAXX (
    GROUPBY (
        ADDCOLUMNS (
            FILTER (
                SUMMARIZE ( ALL ( Data ), 'Name'[Name], 'Calendar'[Date] ),
                'Name'[Name] = MAX ( 'Name'[Name] )
            ),
            "@indexmeasure", [2 Index measure:]
        ),
        [@indexmeasure],
        "@countrow", SUMX ( CURRENTGROUP (), 1 )
    ),
    [@countrow]
) - 1)

[Iam_Uday]

Thanks @Jihwan_Kim for taking time and providing the solution, But for my real data which is close to 1M Rows. This solution of creating measures is not working.

When I get other columns in to the table (Which describe the fact's on Name) - Power Bi is running out of memory and even final measure to show No. of max days a student is present consecutively is taking quite a bit of time.

[daXtreme]

Here's a measure but I still have to test/debug it. If you want to try while it's in the making... This is averaging the max streaks over the names but if you drop the names on the canvas, it'll give you exactly what you want - the longest streak for the name. Please note there's no CALCULATE in there, so if you have thousands of Names in a visual it should be quick. 

EDIT: OK, I've checked it - does work. Now try to see how long it'll take on your dataset.

EDIT2: It takes milliseconds to calculate this measure on a set with 1M rows.

Avg Max Days Present = 
AVERAGEX(
    VALUES( T[Name] ),
    var CurrentName = T[Name]
    var SubsetOfinterest =
        FILTER(
            T,
            T[Name] = CurrentName
            &&
            T[Present or Absent] = "present"
        )
    var MinDate =
        MINX( SubsetOfinterest, T[Date] )
    var SubsetOfinterestWithOrder =
        ADDCOLUMNS(
            SubsetOfinterest,
            "@Grouper",
                var CurrentDate = T[Date]
                var RankStartingWith0 = 
                    RANKX(
                        SubsetOfinterest,
                        T[Date],
                        CurrentDate,
                        ASC,
                        DENSE
                    ) - 1
                return
                    ( MinDate + RankStartingWith0 )
                        - CurrentDate
        )
    var Groupings =
        GROUPBY(
            SubsetOfinterestWithOrder,
            [@Grouper],
            "@RowCount", SUMX( CURRENTGROUP(), 1 )
        )
    var MaxStreak =
        MAXX(
            Groupings,
            [@RowCount]
        )
    return
        MaxStreak
)