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.

paulvans182's avatar
paulvans182
Helper III
6 years ago

Hi Icey ,

 

Thank you so much for your response.  The solution that you have provided is almost working perfectly, however, I am getting the following issue:

  1. The consecutive wins for some reason are in reverse order

 

I literally have copy/pasted your DAX expression from your response. Could you please let me know if there is an additional modification I need to make?

 

Thank you once again for your help with this problem 

 

Kind regards,

Icey's avatar
Icey
Community Support
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.

  • paulvans182's avatar
    paulvans182
    Helper III
    6 years ago

    Hi Icey ,

     

    Thank you.  It's perfect.  I really appreciate your help with this problem!

     

    Kind regards,

     

    Paul