Hi all,
I have tried this many ways and can't quite get it to work. I need to create a Matrix and exclude the top 25 states based on COUNT() of ID from the State table and the MAX() Year from the Date table. There is a 1:M relationship between the Date table and State Table. Sample tables look like:
State
| State | ID | Cost | Date | Year |
| A | 1 | 10 | 1/1/2022 | 2022 |
| B | 2 | 20 | 1/2/2022 | 2022 |
| C | 3 | 50 | 1/3/2022 | 2022 |
| D | 4 | 5 | 1/4/2022 | 2022 |
| A | 5 | 20 | 1/1/2021 | 2021 |
| B | 6 | 40 | 1/2/2021 | 2021 |
| C | 7 | 60 | 1/3/2021 | 2021 |
| A | 8 | 20 | 2/1/2022 | 2022 |
| A | 9 | 30 | 1/1/2021 | 2021 |
| A | 10 | 40 | 1/1/2020 | 2020 |
| B | 11 | 40 | 1/1/2020 | 2020 |
| B | 12 | 20 | 2/1/2022 | 2022 |
Date
| Date | Year |
| 1/1/2020 | 2020 |
| 1/2/2020 | 2020 |
| 1/3/2020 | 2020 |
| 1/4/2020 | 2020 |
| 1/1/2022 | 2022 |
| 1/2/2022 | 2022 |
| 1/3/2022 | 2022 |
| 1/4/2022 | 2022 |
The table without excluding the top 25 States and Max year Looks like this:
| State | COUNT_ID | SUM_Cost |
| A | 5 | 30 |
| B | 4 | 40 |
| C | 1 | 50 |
| D | 1 | 5 |
I need it to look like this:
| State | COUNT_ID | SUM_COST |
| C | 1 | 50 |
| D | 1 | 5 |
The table should respond to all filters that are applied. I have tried many different versions using RANKX() and TOPN(), but it never seems to work out. I think the problem is that TOPN() and RANKX() are calculating across all years and not the MaxYear (which is a measure) from the Date table.
Any help is appreciated.
Thank you
