Exclude TOPN() or top RANKX() from Matrix

Data-Hound — Fri, 03 Nov 2023 19:30:59 GMT

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

Re: Exclude TOPN() or top RANKX() from Matrix

FreemanZ — Sat, 04 Nov 2023 02:50:48 GMT

Hi Data-Hound ，

Having problem making consistent understanding of the description and the sample data.

topic Re: Exclude TOPN() or top RANKX() from Matrix in DAX Commands and Tips

Exclude TOPN() or top RANKX() from Matrix

Re: Exclude TOPN() or top RANKX() from Matrix