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

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

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors