topic Re: Winstreak and losestreak in DAX Commands and Tips

Winstreak and losestreak

Pan_Forex — Sat, 07 Jan 2023 15:55:27 GMT

Hi guys! I counted winstreak and losestreak for each player. I don't know why but some people have 5 winstreaks in a row and 1 loss at the same time which is impossible. My tabel:

woncol = RANKX(FILTER(ALL('table'),'table'[player]=earlier('table'[player])),'public players'[id],,ASC,Dense)

flag = IF('table'[won]= FALSE(), 0, 1)

minflag =

CALCULATE (

MIN ( 'table'[woncol] ),

FILTER (

ALL ( 'table' ),

'public players'[woncol] >= EARLIER ( 'table'[woncol] )

&&'table'[player]=EARLIER('table'[player])

&& 'table'[won] = FALSE()

)

Actual Winstreak =

VAR _1 =

CALCULATE (

COUNT( 'table'[flag] ),

FILTER (

ALL ( 'table' ),

'table'[minflagdate] = SELECTEDVALUE ( 'table'[minflag] )

&& 'table'[player]=SELECTEDVALUE('table'[player] )

&&'table'[flag]=1

)

RETURN

For losestreak I counted everything the same only "flag" changed to false. Almost everything is counted improvement. Any ideas why some players have winstreak and losestreak at the same time?

Re: Winstreak and losestreak

Greg_Deckler — Sat, 07 Jan 2023 16:02:57 GMT

Pan_Forex Can you post your sample data as text? Sounds very similar to Cthulhu. Cthulhu - Microsoft Power BI Community

Re: Winstreak and losestreak

Pan_Forex — Sat, 07 Jan 2023 16:28:34 GMT

Sorry, already correcting it 🙂

player	id	won	flag	minflag	woncol
CmXuTNsOTsmsiFBnPvjVHsXKrT	71299	True	1	145	142
CMuVdfxDLzKisPtIxmyLxiWrcK	71299	True	1	265	260
wpsBViXSSZXtCQSgVhobvcWnW	71299	False	0	131	131
qPInEuDBbHaRKJzpFeIzoWXfg	71299	False	0	89	89
BGkrFysZHxcdxGIVxEEBPzETWB	71299	True	1	2	1
BLbgUfRQguMPWFqbvltVMRyvLo	71299	False	0	35	35
CeqRurzZyuIZQAfDkovsdTyfSE	71299	False	0	28	28
CnvQSYdVAJNYaQYJBJxJemHhS	71299	False	0	1	1
oaxLIcZnZWHfMydgZNlCdbty	71299	True	1	58	56
wHouTwXYLGcMnxtNKNUAWUdeu	71258	False	0	125	125
BbamEvAlDaFJIXOpCSvRGXFrrR	71258	True	1	61	60
kYinRhrIqgJFFOgUUcbLvlzBp	71258	True	1	78	76
CWKAuYIXqUGewnxVoKOQDFNmFD	71258	True	1	87	86
CmXuTNsOTsmsiFBnPvjVHsXKrT	71258	True	1	145	141
BGqrNyICmavhBuWEMqgtcEJUoG	71258	False	0	145	145
BZsMiHuibMqWQMrEydfExEJNHD	71258	False	0	59	59
McKRpmzqAifPaQRUFAvnUOSmk	71258	False	0	31	31
CWKAuYIXqUGewnxVoKOQDFNmFD	71238	False	0	85	85
CClLMBwNZpcAsqmxtzANnpdcQz	71238	True	1	51	49
BbamEvAlDaFJIXOpCSvRGXFrrR	71238	True	1	61	59
CeqRurzZyuIZQAfDkovsdTyfSE	71238	True	1	28	27
CmXuTNsOTsmsiFBnPvjVHsXKrT	71238	True	1	145	140
BlePEpyRbwNZnIyQruGzrYKpQh	71238	True	1	149	146
CUHzIOGDNIvSSdRaJObuFafAKI	71238	True	1	45	43
wHouTwXYLGcMnxtNKNUAWUdeu	71238	False	0	124	124
BxyNvbdEMEKGgjhdAviDfbcNMB	71238	False	0	68	68
McKRpmzqAifPaQRUFAvnUOSmk	71238	False	0	30	30
BFiyegijChyTUSuWHWymXEbddj	71238	False	0	70	70
BZsMiHuibMqWQMrEydfExEJNHD	71238	False	0	58	58
WxpXBIdxIOMnWGqwBgBnvCrbt	71210	False	0	6	6
BbamEvAlDaFJIXOpCSvRGXFrrR	71210	True	1	61	58
McKRpmzqAifPaQRUFAvnUOSmk	71210	True	1	30	29
xYeogVArlCXIwphrQIHvaPdr	71210	True	1	83	82
CdroilYGeReIUxBowVOyAjRcmf	71210	True	1	115	112
BJhwYKuoyivSsZSJoknEWsUhaj	71210	True	1	116	115
ClRuaVgEnHWxbcNmtgTSMelizc	71210	True	1	188	187
pVypnhtdAtvonajbXAhFrOlTR	71210	False	0	214	214
BlePEpyRbwNZnIyQruGzrYKpQh	71210	False	0	145	145
CgcBpkSRXhTDKONAOnXnEMiBUX	71210	False	0	43	43
BZsMiHuibMqWQMrEydfExEJNHD	71210	False	0	57	57
ZgZniYcgXRJGTlxFcdgYQilip	71210	False	0	4	4
CeqRurzZyuIZQAfDkovsdTyfSE	71185	False	0	26	26
wHouTwXYLGcMnxtNKNUAWUdeu	71185	True	1	124	123
oaxLIcZnZWHfMydgZNlCdbty	71185	True	1	58	55
CmXuTNsOTsmsiFBnPvjVHsXKrT	71185	True	1	145	139
AWRBkwydVvyprddKLiGSOWnBu	71185	False	0	130	130
CClLMBwNZpcAsqmxtzANnpdcQz	71185	True	1	51	48
BFiyegijChyTUSuWHWymXEbddj	71185	False	0	69	69
qaHhhMuePZpHoEzJesKDxnsFk	71185	False	0	39	39
CozIhMpsYNyHIRplaWHZPUmCFe	71185	False	0	51	51
BOjJZjEwLutnSLwSiUWBtSMguS	71185	True	1	2	1
CgcBpkSRXhTDKONAOnXnEMiBUX	71111	False	0	42	42
BvCSeeIdgqntLZYiCgtFUbeRXc	71111	True	1		3
CjEkXqDbooVQBViOblFwHUJWTW	71111	True	1	8	6
BIvZifBrQczctPGfXCzNDcsFjE	71111	True	1	54	53
xYeogVArlCXIwphrQIHvaPdr	71111	True	1	83	81
CXBhFVOjGfnVJFNdzMufDJzRW	71111	True	1	156	154
BlePEpyRbwNZnIyQruGzrYKpQh	71111	False	0	144	144
BoeGzypZvxDZHKMSGXydYOkQki	71111	False	0	11	11
CeqRurzZyuIZQAfDkovsdTyfSE	71111	False	0	25	25
xDetFXBJFKjnYKftlBfxcGdaJ	71111	False	0	42	42
BTMVGyfMBhiSUijJkeEikGtqoX	71065	True	1	161	159
BlePEpyRbwNZnIyQruGzrYKpQh	71065	True	1	144	143
BpDwEbgochrQINStyTcCmgrtsu	71065	False	0	208	208
rQHjbRucPloxzhrAJJVXqhxvS	71065	False	0	130	130
eKuYDDRHUVFPnJGmPpTgUhECN	71065	False	0	67	67
WBvhxhUpkoapgdRXQIdlwbYsh	71065	False	0	8	8
BUWSmAGUOlFCCtgRZCvfCAwPiX	71065	True	1	88	85
CjEkXqDbooVQBViOblFwHUJWTW	71065	True	1	8	5
BbamEvAlDaFJIXOpCSvRGXFrrR	71059	False	0	57	57
BMBWqEeLJXWDVFnChXaYkXdbSu	71059	False	0	23	23
xDetFXBJFKjnYKftlBfxcGdaJ	71059	True	1	42	41
XzpVlpdZcSInwzeSHIOkRomeP	71059	True	1	61	60
wpsBViXSSZXtCQSgVhobvcWnW	71059	True	1	131	130
qPInEuDBbHaRKJzpFeIzoWXfg	71059	True	1	89	88
IKyIsKztvPPcdWcAHaLRCQOxh	71059	True	1	212	211
CWKAuYIXqUGewnxVoKOQDFNmFD	71059	False	0	84	84
BgkPZMaMuuMwiXXwvTIwLySBmV	71059	False	0	33	33
CGTkqXhGqtLHMgPyUMmVklqYgQ	71059	False	0	42	42

Re: Winstreak and losestreak

Pan_Forex — Sat, 07 Jan 2023 19:06:18 GMT

Sorry, there is a pbix file with the whole table in the link: https://we.tl/t-4sJj8zcENK

Re: Winstreak and losestreak

Greg_Deckler — Tue, 10 Jan 2023 11:59:08 GMT

Pan_Forex Not certain this is what you are looking for but you could try this (see below). You could combine this with a measure that is simply MAX('Table'[Win Streak Column])

Win Streak Column = VAR __Player = [player] VAR __id = [id] VAR __Table = FILTER(ALL('Table'),[player] = __Player && [id] <= __id) VAR __Loss = MAXX(FILTER(__Table,[won] = FALSE()),[id]) VAR __Result = IF(__Loss = BLANK(),COUNTROWS(__Table),COUNTROWS(FILTER(__Table, [id] > __Loss))) RETURN __Result

Re: Winstreak and losestreak

Pan_Forex — Wed, 11 Jan 2023 16:30:39 GMT

Hello Greg_Deckler, Thank you very much for your time. Are you sure the function is correct? Unfortunately it displays me an error that the syntax is not correct.

Re: Winstreak and losestreak

Greg_Deckler — Wed, 11 Jan 2023 17:35:14 GMT

Pan_Forex It's correct in my PBIX attached below signature. So, pretty sure.