cancel
Showing results for
Did you mean:
Highlighted
Member

## DAX formula for consecutive wins

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: DAX formula for consecutive wins

@PetyrBaelish

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
)
)
)
```

Try my new Power BI game Cross the River
2 REPLIES 2
Super User III

## Re: DAX formula for consecutive wins

@PetyrBaelish

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
)
)
)
```

Try my new Power BI game Cross the River
Super User III

## Re: DAX formula for consecutive wins

@PetyrBaelish

Now in a Table Visual you can easily select MAX of consecutive WINS

Please see attached file

Try my new Power BI game Cross the River

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors