Winstrike

[Pan_Forex]

Hello guys, I have no idea how to count winstrike. I have two columns, user and won (true or false). I would like to count the longest winning streak for each user and the actual.

[Pan_Forex]

Hi  @Anonymous  thank you very much for your time! Unfortunately, I did not specify that users are not in sequence and it's not working in my case. Below I add what a piece of the table looks like:

user	won	index
A	true	20
B	true	19
C	false	18
B	true	17
A	false	16
C	false	15
C	false	14
A	true	13
A	true	12
D	true	11
E	false	10
E	true	9
E	true	8
A	true	7
C	true	6
C	true	5
A	false	4
A	false	3
E	true	2
E	false	1

In my case, users A and E have the highest winstrike and it is 3

[Anonymous · Accepted solution]

Hi @Pan_Forex ,

I have created a simple smaple, please refer to it to see if it helps you.

Create columns.

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

minflagdate = 
CALCULATE (
    MIN ( 'Table'[woncol] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[woncol] >= EARLIER ( 'Table'[woncol] )
        &&'Table'[user]=EARLIER('Table'[user])
            && 'Table'[won] = FALSE()
    )
)

woncol = RANKX(FILTER(ALL('Table'),'Table'[user]=earlier('Table'[user])),'Table'[index],,ASC,Dense)

Then create measures.

MM = 
VAR _1 =
    CALCULATE (
        COUNT( 'Table'[flag] ),
        FILTER (
            ALL ( 'Table' ),
            
                 'Table'[minflagdate] = SELECTEDVALUE ( 'Table'[minflagdate] )
                && 'Table'[user]=SELECTEDVALUE('Table'[user] )
                &&'Table'[flag]=1
        )
    )
RETURN
   _1

Measure = MAXX(FILTER(ALL('Table'),'Table'[user]=SELECTEDVALUE('Table'[user])),[MM])

Measure 2 = RANKX(ALL('Table'),CALCULATE([Measure]),,DESC,Skip)

Finally filter the measure 2 is 1.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pan_Forex]

Everything works perfectly, thank you very much for your time and great job 🙂

[Anonymous]

Hi @Pan_Forex ,

I have created a simple sample, please refer to it to see if it helps you.

Create columns.

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

minflagdate = 
CALCULATE (
    MIN ( 'Table'[Index] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Index] >= EARLIER ( 'Table'[Index] )
            && 'Table'[won] = FALSE()
    )
)

Then create measures.

MM = 
VAR _1 =
    CALCULATE (
        SUM( 'Table'[flag] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]<= SELECTEDVALUE ( 'Table'[Index])
                && 'Table'[minflagdate] = SELECTEDVALUE ( 'Table'[minflagdate] )
                && 'Table'[ user ] = SELECTEDVALUE ( 'Table'[ user ] )
        )
    )
RETURN
    IF ( MAX ( 'Table'[flag] ) = 0, 0, _1 )

Measure = MAXX(FILTER(ALL('Table'),'Table'[ user ]=SELECTEDVALUE('Table'[ user ])),[MM])

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.