Rank Multiple Criteria with RLS

[Anonymous]

Hello All,

I am having issues setting up rankings within my dataset. Basically I have results coming in by month with full hierarchy, and I need National Rank and Region Rank by Month. On top of that, I am using RLS in the report so I'm assuming these rankings need to be done in the dataset itself as calculated columns rather than by using measures. I need the user to see their national rank despite the security limiting their report view to their Market.

Here's an example of how my data is set up along with the desired results I am trying to get from the calculated rank columns...

Month	Region	Market	Dealer	% Target	National Rank	Region Rank
1/31/2020	Region 1	Market 1	Steve	101%	1	1
1/31/2020	Region 1	Market 2	Bob	99%	2	2
2/29/2020	Region 2	Market 3	Bill	110%	1	1
2/29/2020	Region 1	Market 1	Steve	95%	4	2
2/29/2020	Region 1	Market 2	Bob	105%	2	1
1/31/2020	Region 2	Market 3	Bill	98%	3	1
2/29/2020	Region 2	Market 1	Bryan	103%	3	2

[Anonymous · Accepted solution]

Something like this?

[National Rank] =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[% Target] <= EARLIER ( 'Table'[% Target] )
    )
)

[Region Rank] =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Month] = EARLIER ( 'Table'[Month] )
            && 'Table'[Region] = EARLIER ( 'Table'[Region] )
            && 'Table'[% Target] <= EARLIER ( 'Table'[% Target] )
    )
)

[Anonymous]

Worked like a charm, thanks!