Forum Discussion
Helper INational ,Regional and Territory Ranks
Greeting Folks!
My question might have been asked previously in the forum, however I didn't get a solution.
I have a measure which ranks by Region(3 distinct region , 18 distinct District and 130 distinct Territory.
I have to show 2 ranks
1. Rank when no filter is applied(i.e. Rank for all Region,All District and all Territory)
2. Rank which gets affected by Region, District and Territory filters (slicers)
Below is a snip of what I want to acheive :
Boosted S2 GA is the measure . So Rank column should get affected from Region,territory and District slicer and Rank Region National and Rank District National should remain same and unaffected by Region,District and Territory slicers.
Example : When Region Slicer is filtered to Northeast , Rank should be 1 and Rank Region National should be 2.
Similarly Rank in below table should be based on Districts filtered under Northeast region(slicer). If only 1 dostrict then 1 otherwise rank based on Measure for those filtered districts.
Any help is highly appreciated
regards,
3 Replies
- amitchandak
Super User
bhurru , Meausre Rank is context sensitive
for the first one
rankx(All(Table[region]),[meausre])
rankx(Allselected(Table[region]),[meausre])
For second table
rankx(filter(Allselected(Table[region]),Table[district] =max(Table[district])),[meausre])
rankx(filter(All(Table[region]),Table[district] =max(Table[district])),[meausre])
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
bhurruThanks ..but it didn't worked.
I get 1s across rows
Hi bhurru,
I'd like to suggest you take a look at the following blog about all functions, it may help you to understand these formulas and know more about how they work:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPTRegards,
Xiaoxin Sheng
