Forum Discussion
Baseball Stats
I'd like some help summarizing baseball statistics utilizing a DAX expression to help me find the following
- # H last 1 game / 3 games / 5 games
- # AB last 1 game / 3 games / 5 games
- Etc - might be RBI, PA.......
| GP | PA | AB | H | 2B | 3B | HR | RBI | R | BB | SO | Name |
| 1 | 2 | 2 | 2 | 2 | 0 | 0 | 2 | 1 | 0 | 0 | Player 1 |
| 1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | Player 2 |
| 1 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Player 3 |
| 2 | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | Player 1 |
| 2 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | Player 2 |
| 2 | 2 | 2 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | Player 3 |
| 3 | 3 | 2 | 2 | 0 | 0 | 0 | 2 | 2 | 1 | 0 | Player 1 |
| 3 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | Player 2 |
| 3 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | Player 3 |
| 4 | 2 | 2 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | Player 1 |
| 4 | 2 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | Player 2 |
| 4 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | Player 3 |
| 5 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 3 | 0 | Player 1 |
| 5 | 3 | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | Player 2 |
| 5 | 2 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Player 3 |
| 6 | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | Player 1 |
| 6 | 2 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | Player 2 |
| 6 | 2 | 2 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | Player 3 |
| 7 | 3 | 2 | 1 | 0 | 1 | 0 | 2 | 2 | 0 | 1 | Player 1 |
| 7 | 3 | 1 | 1 | 0 | 0 | 0 | 0 | 2 | 1 | 0 | Player 2 |
| 7 | 3 | 2 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | Player 3 |
1 Reply
To find the number of hits (H) and at-bats (AB) for each player in the last 1, 3, and 5 games, you can use the following DAX expressions:
- H last 1 game: CALCULATE(SUM([H]), FILTER(ALL(Table), [GP] = MAX([GP])))
- H last 3 games: CALCULATE(SUM([H]), FILTER(ALL(Table), [GP] >= MAX([GP]) - 2))
- H last 5 games: CALCULATE(SUM([H]), FILTER(ALL(Table), [GP] >= MAX([GP]) - 4))
- AB last 1 game: CALCULATE(SUM([AB]), FILTER(ALL(Table), [GP] = MAX([GP])))
- AB last 3 games: CALCULATE(SUM([AB]), FILTER(ALL(Table), [GP] >= MAX([GP]) - 2))
- AB last 5 games: CALCULATE(SUM([AB]), FILTER(ALL(Table), [GP] >= MAX([GP]) - 4))
These expressions use the CALCULATE, SUM, FILTER, ALL, and MAX functions to calculate the total number of hits and at-bats for each player in the specified number of games. You can learn more about these functions from the DAX reference.
You can use a similar approach to find other statistics such as RBI, PA, etc. For example, to find the RBI last 3 games, you can use:
- RBI last 3 games: CALCULATE(SUM([RBI]), FILTER(ALL(Table), [GP] >= MAX([GP]) - 2))
I hope this helps you with your baseball analysis. If you have any other questions, please feel free to ask. 😊
