Hello -
So I've been struggling trying to get a measure to produce the output I'm looking for. The reason being the context on my filters is too narrow, and I'd like to broaden the data it's calculating.
I have a table called "Matchup". Looks like this:
| Matchup_id | Home_id | Away_id |
| 1 | 10 | 11 |
| 2 | 15 | 16 |
I've related it to a "Game" table using the Matchup_id. In the Game table, I break apart the home/away parts of the "Matchup" and give them unique records. Looks like this:
| Game_id | Matchup_id | Team_id |
| 1 | 1 | 10 |
| 2 | 1 | 11 |
| 3 | 2 | 15 |
| 4 | 2 | 16 |
I then have a table for plays within the games called "Play". I relate the Play table to the Game table with the Game_id. Looks like this:
| Play_id | Game_id | Stat | Count |
| 1 | 1 | Run | 4 |
| 2 | 1 | Pass | 2 |
| 3 | 2 | Pass | -3 |
| 4 | 2 | Pass | 4 |
| 5 | 3 | Run | 15 |
| 6 | 3 | Pass | 12 |
| 7 | 4 | Run | 1 |
| 8 | 4 | Run | 2 |
This makes it easy to go from Matchup to Play while dilineating by team through the Game table, so getting individual game statistics is simple. Where I'm running into issues is I'd like to build out some visuals to preview upcoming matchups. I want to compare teams in the Matchup by calculating season long statistics in the preview, however, I'm having trouble getting around the Matchup/Game context to get them. Basically if Michigan and Ohio State are the two teams in an upcoming matchup, I want to be able to see their past stats in other contexts like run/pass splits, several seasons, etc.
I'm using the Matchup as a slicer to get to Game, so I think it's a matter of removing that context entirely to get a broader look at the teams' stats historically.
Thanks!
