cancel
Showing results for
Did you mean:
New Member

Count Maximum Consecutive Occurrences of a value

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

1 ACCEPTED SOLUTION
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] )
)
)
)``````

Measure

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

4 REPLIES 4
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] )
)
)
)``````

Measure

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

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 (
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

New Member

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.

Resolver III

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 =
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
)``````