cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RichardJ
Resolver III
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'

 

Consecutive holes in a game.JPG

 

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

1 ACCEPTED SOLUTION
Fowmy
Super User IV
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 🙂

YouTube  LinkedIn

  

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
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 🙂

YouTube  LinkedIn

  

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

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

@Fowmy - Thank you for the excellent response. 

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

Fowmy
Super User IV
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 🙂

YouTube  LinkedIn


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

Proud to be a Super User!

Website   YouTube    LinkedIn

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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