Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |