cancel
Showing results for
Did you mean:
Resolver III

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

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.

Thanks,

Richard

1 ACCEPTED SOLUTION
Super User IV

@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 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Super User IV

@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 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Resolver III

@Fowmy - Thank you for the excellent response.

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

Super User IV

@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 🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Resolver III

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

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors