Count if final value in column equals a specified text, with filter applied

[mkins]

Hi there,

I have the results from a table tennis match where in one column I have the Game Score (Game 1, Game 2, Game 3, etc), and Point Won By column (the name of the player who wins the point).

I would like to be able to calculate how many games each player wins in the match. This would require me to use some DAX and COUNTROWS where the Game Score = "Game 1" (this maybe recorded 20 times if there were 20 points in the game) and the final instance of this value I refer to the Point Won By column to see who that player was. This is because who ever wins the final point in Game 1 will have therefore won this game. 

I hope this makes sense, and any help would be much appreciated.

Thanks you,

M

[v-yaningy-msft · Accepted solution]

Hi, @mkins 

Thank you very much for @danextian reply. Based on your description, I have created a new column to achieve the effect you are looking for. Following picture shows the effect of the display.

New column:

Winner =

VAR CurrentGame = 'Game'[Game]

VAR CurrentIndex = 'Game'[Index]

VAR MaxIndexInSameGame =

    CALCULATE (

        MAX ( 'Game'[Index] ),

        FILTER ( 'Game', 'Game'[Game] = CurrentGame )

    )

RETURN

    IF ( CurrentIndex = MaxIndexInSameGame, 1, BLANK () )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[danextian]

Hi @mkins 
As always, please provide a workable sample data (not an image) and your expected result from that.

[MKins_86]

Hi @danextian thank you for reaching out.

Please fsee example table below. As player A wins the last instance of Game 1, and Game 3, their score equals 2. Player B wins the last instance/ point in Game 2, so their score is 1. Would like to know what DAX is needed to calculate this.

Hope this makes sense.

Thanks

Game	Point Won by
1	Player B
1	Player A
1	Player A
2	Player A
2	Player B
2	Player B
3	Player A
3	Player A
3	Player A