Calculating consecutive holes won in a game of golf for a specific player

[RichardJ]

Hi,

I'd appreciate any help with how to identify the consecutive number of holes won by a player per game of golf.

it's assumed that there will only be one game played per day.

e.g. On the 4th september - the person with the initials of "JS" would have a value of 2 in the longest streak column (at hole 6) due to them having won both holes 5 and 6. The winning streak would be broken at hole 7 on the 4th September when as hole 7 was not won by JS but has a value of 'Draw'

Please ignore the formula i have in the 'Longest Streak' column in the sample file - this was just me messing about trying to work my way towards a solution.

PBIX link : https://www.dropbox.com/s/dobz2gd1wthtt64/golf.pbix?dl=0

Thanks,

Richard

[Fowmy · Accepted solution]

@RichardJ 

Add a New Column to the table: Consistency_Data to identify the consec values

Consec = 
VAR CP = [Value]
VAR DT = [Date]
VAR HO  = [Hole Number]
VAR PREHO = MAXX( FILTER( Consistency_Data , Consistency_Data[Date]=DT && Consistency_Data[Hole Number] < HO), Consistency_Data[Hole Number])
VAR NEXTHO = MINX( FILTER( Consistency_Data , Consistency_Data[Date]=DT && Consistency_Data[Hole Number] > HO), Consistency_Data[Hole Number])
VAR PREVAL = MAXX( FILTER( Consistency_Data , Consistency_Data[Date]=DT && Consistency_Data[Hole Number] = PREHO), Consistency_Data[Value])
VAR NEXTVAL = MAXX( FILTER( Consistency_Data , Consistency_Data[Date]=DT && Consistency_Data[Hole Number] = NEXTHO), Consistency_Data[Value])

RETURN
IF( AND(CP<>"Draw",  CP = PREVAL || CP= NEXTVAL) , 1, 0)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[RichardJ]

@Fowmy - Thank you for the excellent response. 

I wouldn't have thought of using that approach so appreciate the lesson.

[Fowmy]

@RichardJ 

I am having a look at your question, So, what would be your expected result, is it a flag on a new column that shows when any value other than Draw appears consecutively within a day. I guess JS,SH . . are players. Why hole number 16 and 17 are blank.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[RichardJ]

Hi @Fowmy,

I have no preference whether the result is calculated in a Measure or a Column but the desired end result is to show

a card which displays the player with the longest consecutive number of holes won

a matrix which shows a leaderboard of the longest consecutive number of holes won (i.e each player can see their result and determine how far they are from the current leader)

JS and SH are the initials of the players - i'd be happy to change the structure of the data if it helps.

Holes at the later stages of the game may be blank if the game wasn't finished for whatever reason (daylight/weather/etc)

Thanks for taking the time to help.

Cheers,

Richard