cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Iam_Uday
New Member

Count Maximum Consecutive Occurrences of a value

Hi,

I have a input table in this format,

DateNamePresent or Absent
5/1/2022APresent
5/1/2022BPresent
5/2/2022APresent
5/2/2022BPresent
5/3/2022APresent
5/3/2022BPresent
5/4/2022APresent
5/4/2022BAbsent
5/5/2022APresent
5/5/2022BPresent
5/6/2022AAbsent
5/6/2022BPresent

 

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

NameHighest No.of days Continuously Present
A5
B3


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

1 ACCEPTED SOLUTION
tamerj1
Community Champion
Community Champion

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] ) 
        ) 
    )
)

1.png

Measure

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

2.png

View solution in original post

4 REPLIES 4
tamerj1
Community Champion
Community Champion

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] ) 
        ) 
    )
)

1.png

Measure

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

2.png

Jihwan_Kim
Super User
Super User

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)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

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
)

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors