Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rank Multiple Criteria with RLS

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...

MonthRegionMarketDealer% TargetNational RankRegion Rank
1/31/2020Region 1Market 1Steve101%11
1/31/2020Region 1Market 2Bob99%22
2/29/2020Region 2Market 3Bill110%11
2/29/2020Region 1Market 1Steve95%42
2/29/2020Region 1Market 2Bob105%21
1/31/2020Region 2Market 3Bill98%31
2/29/2020Region 2Market 1Bryan103%32
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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] )
    )
)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Not applicable

Worked like a charm, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors