The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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.
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
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.
Everything works perfectly, thank you very much for your time and great job 🙂
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.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |