Calculated column or measure to get win streak

[pontushaglund]

Hi all.

I'm trying to create a calculated column (or measure) to give me current win streak. I've vacuumed forums and blog posts online to try to find what I am searching for and the only post that should help me (https://forum.powerpivotpro.com/forums/topic/streaks-winninglosing-or-simply-consecutive-instances-o...) cannot help me - when implementing it I get an out of resources exception in Power BI Desktop (I'm on x64 client with 16 GB RAM).

Simplification of my data (edited with correct Index):

Index | IsWin | WinStreak (not implemented)

1        |    0     |       0

2        |    1     |       1

3        |    1     |       2

4        |    1     |       3

5        |    0     |       0

6        |    1     |       1

7        |    1     |       2

8        |    0     |       0

9        |    0     |       0

The third column is what I am trying to implement (and cannot seem to get my head around).

I am perfectly happy with "pseudo-dax" to help me out.

Thanks,

Pontus

[Greg_Deckler]

Is your Index really supposed to be all 1's?

[pontushaglund]

Duh. Thanks.

Index is of course

1

2

3

4

5

...

[Greg_Deckler]

Can you post your current formula? I am working on one and have made some progress but would like to try yours to see if I can duplicate your issue.

[malagari]

 My approach is to break down what a Win Streak actually is?  It's the difference between the Last Loss Game # and the Current Game #.

WinStreak =

var LastLossIndex = CALCULATE( MAX(Index),
                               FILTER(Table, [IsWin] = 0),
                               FILTER(Table, [Index] <= EARLIER([Index])
                    )

return
[Index] - LastLossIndex

This calculates the LastLossIndex based on each row of the table, and then a simple difference between the current row context's Index and the LastLossIndex.

Here's the result in a sample PBIX:

[pontushaglund]

Firstly - thanks for engaging and sorry about the delay in my response. I've done some extensive testing and come up with the following:

1. Your proposed calculated column works fine for small datasets. When trying to add it to my 63000-row table it gives me the error message 'There's not enough memory to complete this operation. Please try again later when there may be more memory available.'.

2. I designed the following measure that works fine as long as I only every single value. I have a datetime value on each row as well and if I want the Max Streak for a year/month/quarter etc I get incorrect values (I get the max Index that year).

Streak using MAXX =
MAX( Table1[Index])-

MAXX(
   FILTER( ALL(Table1); Table1[Index] <= MAX( Table1[Index]) && Table1[Överskridande] = 0);
   Table1[Index]
)

What I would like but don't know how to achieve:

Streak using MAXX alternative = 
MAXX(
   FILTER( ALL(Table1); Table1[Index] <= MAX( Table1[Index]) && Table1[Överskridande] = 0);
   MAX( Table1[Index]) - Table1[Index]
)

where the bold part would be the current row Index in the interation.

Other ideas?

[pontushaglund]

It's from here: https://forum.powerpivotpro.com/forums/topic/streaks-winninglosing-or-simply-consecutive-instances-o...

=
SWITCH (
TRUE (),
[WinValidation] = 0, 0,
CALCULATE (
COUNTROWS ( factGameTeamsPlayers ),
FILTER (
ALL ( factGameTeamsPlayers ),
[WinValidation] = 0
&& [PlayerID] = EARLIER ( [PlayerID] )
&& [GameSequenceNum] < EARLIER ( [GameSequenceNum] )
)
) = 0,
CALCULATE (
SUM ( [WinValidation] ),
FILTER (
ALL ( factGameTeamsPlayers ),
[PlayerID] = EARLIER ( [PlayerID] )
&& [GameSequenceNum] <= EARLIER ( [GameSequenceNum] )
)
),
CALCULATE (
SUM ( [WinValidation] ),
FILTER (
ALL ( factGameTeamsPlayers ),
[PlayerID] = EARLIER ( [PlayerID] )
&& [GameSequenceNum]
> CALCULATE (
MAX ( [GameSequenceNum] ),
FILTER (
ALL ( factGameTeamsPlayers ),
[WinValidation] = 0
&& [PlayerID] = EARLIEST ( [PlayerID] )
&& [GameSequenceNum] < EARLIEST ( [GameSequenceNum] )
)
)
&& [GameSequenceNum] <= EARLIER ( [GameSequenceNum] )
)
)
)

I modified it by leaving out the Player filter as I do not have that additional dimension in my data.

My dataset has 63000 rows.