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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors