cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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:

PBI - consecutive wins.PNG

 

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


Consecutibe.png

Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Super User III
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
            )
        )
    )


Consecutibe.png

Try my new Power BI game Cross the River

View solution in original post

Super User III
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

 

maxconsec.png

Try my new Power BI game Cross the River

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

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!

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

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