Forum Discussion

paulvans182's avatar
paulvans182
Icon for Helper III rankHelper 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
Icon for Community Support rankCommunity Support
6 years ago

Hi paulvans182 ,

I modify your DAX expression like so:

Consecutive Wins = 
VAR Ref_Game_ID =
    CALCULATE (
        MIN ( '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 (), 1000, 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] )
        )
    )

And get something below:

 

Best Regards,

Icey

 

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