Forum Discussion

mkins's avatar
mkins
Icon for Helper I rankHelper I
2 years ago
Solved

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

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

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

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

    • MKins_86's avatar
      MKins_86
      Frequent Visitor
      2 years ago

      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

      GamePoint Won by
      1

      Player B

      1Player A
      1Player A
      2Player A
      2Player B
      2Player B
      3Player A
      3Player A
      3Player A
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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