Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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] )
)
)
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] )
)
)
Worked like a charm, thanks!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |