Forum Discussion

paulvans182's avatar
paulvans182
Helper III
6 years ago
Solved

Calculated Column: Consecutive Wins Column

Good afternoon,   I am trying to create a column that calculates the consecutive wins by a player.  I have the following columns in my data:  Game ID, Player, Game Board, Results (W/L), Category,...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi paulvans182 ,

    I find that!😉 Please try this:

    Consecutive Wins =
VAR Ref_Game_ID =
    CALCULATE (
        MAX ( 'Game Data'[Game ID] ),
        FILTER (
            'Game Data',
            'Game Data'[Player] = EARLIER ( 'Game Data'[Player] )
                && 'Game Data'[Game ID] < EARLIER ( 'Game Data'[Game ID] )
                && 'Game Data'[Results] <> "W"
        )
    )
VAR Ref =
    IF ( Ref_Game_ID = BLANK (), 'Game Data'[Game ID] - 1, Ref_Game_ID )
RETURN
    IF (
        'Game Data'[Results] = "W",
        DIVIDE (
            COUNTROWS (
                FILTER (
                    'Game Data',
                    'Game Data'[Player] = EARLIER ( 'Game Data'[Player] )
                        && 'Game Data'[Game ID] <= EARLIER ( 'Game Data'[Game ID] )
                        && 'Game Data'[Game ID] > Ref
                )
            ),
            DISTINCTCOUNT ( 'Game Data'[Category] )
        )
    )

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Icey's avatar
Icey
Community Support
6 years ago

Hi paulvans182 ,

Try this:

Consecutive Wins Measure = 
    VAR Ref_Game_ID = 
        CALCULATE(
            MIN('Game Data'[Game ID]),
            FILTER(
                'Game Data',
                'Game Data'[Player] = MAX('Game Data'[Player])
                && 'Game Data'[Game ID] > MAX('Game Data'[Game ID])
                && 'Game Data'[Results] = "W"
            )
        )
    VAR Ref = 
        IF(Ref_Game_ID = BLANK(),1000,Ref_Game_ID)
Return
    If(
        MAX('Game Data'[Results]) = "W",
        CALCULATE(
            DISTINCTCOUNT('Game Data'[Results]),
            FILTER('Game Data',
                'Game Data'[Player] = MAX('Game Data'[Player])
                && 'Game Data'[Game ID] >= MAX('Game Data'[Game ID])
          //      && 'Game Data'[Game ID] < Ref
            )
        )
    )

 This is a measure, not a calculated column.

If it doesn't work, please share me more sample data.

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.