Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

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


Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

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