Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a measure that will calculate a person's consecutive wins from the data below (this is in a table called Results with the columns as detailed in the picture below:
The results are for a weekly rournament and the result column contains the result - if somebody turns up late there is a "(L)" at the end of the result to signify this. The columns 1st, 2nd & 3rd are measures based on the result column.
So looking at the table above Dan was first on 2 consecutive weeks, so the final result will need to show that Dan has 2 consecutive wins.
Laura won on 15/10/18, she then didn't play on 22/10/2018 and won again on 29/10/2018. She therefore has 2 consecutive wins also.
Steve also has one win.
I'm hoping to achieve the following output:
Player / Consecutive Win
Laura / 2
Dan / 2
Steve / 1
Another scenario is that each player will have different runs - so Dan might win 3 in a row in November for example in which case I'd be interested in his Maximum consecutive wins (his 3 in November rather than his 2 in October)
I'm not interested in anybody that hasn't won - I guess these would be filtered out in my report.
I was thinking of creating a measure on the results table (but I'm unsure of the formula to use) - on a report I'd then group it by player and display the MAX consecutive wins.
Any advice would be much appreciated.
Solved! Go to Solution.
This approach will hopefull work
First Add a calculated column like this.
The purpose of this column is to identify the number of consecutive wins at each win
Consecutive Wins = VAR Ref_Date = CALCULATE ( MIN ( Table1[Tournament] ), FILTER ( Table1, [Player] = EARLIER ( [Player] ) && [Tournament] > EARLIER ( [Tournament] ) && LEFT ( [Result], 3 ) <> "1st" ) ) VAR Ref = IF ( Ref_Date = BLANK (), DATE ( 3000, 12, 31 ), Ref_Date ) RETURN IF ( LEFT ( [Result], 3 ) = "1st", COUNTROWS ( FILTER ( Table1, [Player] = EARLIER ( [Player] ) && [Tournament] >= EARLIER ( [Tournament] ) && [Tournament] < Ref ) ) )
This approach will hopefull work
First Add a calculated column like this.
The purpose of this column is to identify the number of consecutive wins at each win
Consecutive Wins = VAR Ref_Date = CALCULATE ( MIN ( Table1[Tournament] ), FILTER ( Table1, [Player] = EARLIER ( [Player] ) && [Tournament] > EARLIER ( [Tournament] ) && LEFT ( [Result], 3 ) <> "1st" ) ) VAR Ref = IF ( Ref_Date = BLANK (), DATE ( 3000, 12, 31 ), Ref_Date ) RETURN IF ( LEFT ( [Result], 3 ) = "1st", COUNTROWS ( FILTER ( Table1, [Player] = EARLIER ( [Player] ) && [Tournament] >= EARLIER ( [Tournament] ) && [Tournament] < Ref ) ) )
Now in a Table Visual you can easily select MAX of consecutive WINS
Please see attached file
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |