Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Is your Index really supposed to be all 1's?
Duh. Thanks.
Index is of course
1
2
3
4
5
...
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.
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:
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?
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.
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |